mysql - To count and return multiple row in single sql query and print result as table
问题描述
I have a doubt in related to sql query. I will explain it down
**Table People**
| S.No| name|task|
| ----| ----|----|
| 1 | n1 |t1 |
| 2 | n2 |t2 |
| 3 | n3 |t1 |
| 4 | n4 |t1
| 5 | n5 |t2 |
**Table Tasks**
| S.No| taskid|taskname|
| ----| ----|----|
| 1 | t1 |task1|
| 2 | t2 |task2|
| 3 | t3 |task3|
| 4 | t4 |task4|
| 5 | t5 |task5|
I need to get the count of how many people a task is assigned to like this
**Expected result**
|task |count|
|-----|-----|
|task1| 3 |
|task2| 2 |
|task3| 0 |
I tried a query like this below but It doesn't work
SELECT COUNT(TASK) FROM PEOPLE WHERE TASK IN (SELECT TASKID IN TASKS)
The above query returns overall count of all tasks . What I need to modify in the query to get expected result
解决方案
It's more like:
SELECT t.taskname as task, COUNT(p.task) as `count`
FROM
task t
LEFT JOIN people p ON p.task = t.taskid
GROUP BY
t.taskname
The left join from tasks (gets all task record) to people (might not get any people record, so for eg task3, p.task is null) will certainly contain all tasks, but not all people.. For those tasks that have multiple people, the taskname will repeat multiple times. When it is grouped, it becomes a single one, and we can count the group corrently
It is important to count on p.task
, rather than COUNT(*)
because COUNT(*)
will see a row like:
name, p.task
task3, null
..and if we count(*)
'd it would count as 1, because it is a row.. but we want to count p.task
because NULLs are never counted, so we get a count of 0 for that task
I'd have called people.task
as people.taskid
, personally
I'm not really sure what you mean by "print result as table" - that's a job for the front end really, but if you mean you want to format the output, then you'll need to look at something like CONCAT(...)
or CONCAT_WS(...)
which will allow you to produce formatted text as the output
If your task names are not unique then you can, as @stickybit says in the comments, put t.taskid
in the group by too (or instead; if it's the primary key of the table)
推荐阅读
- python - 在scrapy python中使用非类标签进行选择
- c++ - 为什么我的计时持续时间没有显示正确的值?
- asp.net-mvc - ASP.NET Core MVC:包含 %2F(正斜杠)的路由参数存在问题
- docker - traefik 不遵守前端规则
- c++ - 当我可以使用 BidirectionalIterator 或 RandomAccessIterator 实现相同的目标时,为什么要使用 ForwardIterator
- sql - 在后台刷新 Access VBA 中的表?
- python - 将熊猫矩阵转换为字符串时如何避免“...”?
- python - Python:如何将打印输出重定向到 txt 文件?
- excel - 复制范围,包括从网站到电子邮件的形状
- python-3.x - 具有当前数据库版本设置的“oracle”方言不支持空插入