SQL查询：按不同表中的多个列进行分组(SQL Query: Group by multiple columns in different tables)

我有两个表用户和任务。我需要获取按Users.Name和Tasks.Status分组的任务计数。我需要一些帮助编写查询，使我的结果看起来类似于第三个表。

用户

|- Id -|- Name -| |- 1 -|- Robert -| |- 2 -|- Bob -| |- 3 -|- Vicky -| |- 4 -|- Don -| |- 5 -|- Ron -| |- 6 -|- Harry -|

任务

|- Id -|- Status -|- user_id -| |- 1 -|- Started -|- 1 -| |- 2 -|- Started -|- 1 -| |- 3 -|- Started -|- 2 -| |- 4 -|- Started -|- 2 -| |- 3 -|- Complete -|- 1 -| |- 4 -|- Complete -|- 1 -| |- 5 -|- Complete -|- 2 -| |- 6 -|- Complete -|- 2 -|

结果

|- Name -|- Status -|- Count -| |- Robert -|- Complete -|- 2 -| |- Robert -|- Started -|- 2 -| |- Bob -|- Complete -|- 2 -| |- Bob -|- Started -|- 2 -| |- Vicky -|- Complete -|- 0 -| |- Vicky -|- Started -|- 0 -| |- Don -|- Complete -|- 0 -| |- Don -|- Started -|- 0 -| |- Ron -|- Complete -|- 0 -| |- Ron -|- Started -|- 0 -| |- Harry -|- Complete -|- 0 -| |- Harry -|- Started -|- 0 -|

I have two tables Users and Tasks. I need to get the count of tasks grouped by Users.Name and Tasks.Status. I need some help writing query to make my result look similar to the third table.

Users

|- Id -|- Name -| |- 1 -|- Robert -| |- 2 -|- Bob -| |- 3 -|- Vicky -| |- 4 -|- Don -| |- 5 -|- Ron -| |- 6 -|- Harry -|

Tasks

|- Id -|- Status -|- user_id -| |- 1 -|- Started -|- 1 -| |- 2 -|- Started -|- 1 -| |- 3 -|- Started -|- 2 -| |- 4 -|- Started -|- 2 -| |- 3 -|- Complete -|- 1 -| |- 4 -|- Complete -|- 1 -| |- 5 -|- Complete -|- 2 -| |- 6 -|- Complete -|- 2 -|

Result

|- Name -|- Status -|- Count -| |- Robert -|- Complete -|- 2 -| |- Robert -|- Started -|- 2 -| |- Bob -|- Complete -|- 2 -| |- Bob -|- Started -|- 2 -| |- Vicky -|- Complete -|- 0 -| |- Vicky -|- Started -|- 0 -| |- Don -|- Complete -|- 0 -| |- Don -|- Started -|- 0 -| |- Ron -|- Complete -|- 0 -| |- Ron -|- Started -|- 0 -| |- Harry -|- Complete -|- 0 -| |- Harry -|- Started -|- 0 -|

最满意答案

如果你想在答案中得到零，你需要先生成行。以下是获取结果的方法：

select u.name, s.status, count(t.userid) as cnt from users u cross join (select distinct status from tasks) s left join tasks t on t.userid = u.id and t.status = s.status group by u.name, s.status order by u.name, s.status;

请注意，这是标准SQL，因此它可以在标记中的所有数据库中使用。

If you want to get zero's in the answer, you need to generate the rows first. Here is a method for getting the results:

select u.name, s.status, count(t.userid) as cnt from users u cross join (select distinct status from tasks) s left join tasks t on t.userid = u.id and t.status = s.status group by u.name, s.status order by u.name, s.status;

Note that this is standard SQL, so it will work in all the databases in your tags.

更多推荐