今天在实验楼上面学习sql语句,觉得好有成就感。卡在作业题目上面卡了好久。
题目是这样:
使用连接查询的方式,查询出**各员工所在部门的人数与工程数**,工程数命名为count_project。(连接3个表,并使用COUNT内置函数)
做了很多尝试,列出表的内容等等。
具体尝试内容可以查看这里:https://www.shiyanlou.com/courses/reports/549775
最终在网上找到了答案:http://goobbe.com/questions/5113072/mysql-multiple-count-from-multiple-tables-with-left-join
撒花!做出来了好开心!
sql语句如下:
SELECT department.dpt_name,
COUNT(DISTINCT project.proj_name) AS 'count_project',
COUNT(DISTINCT employee.name) AS 'count_people'
FROM department
LEFT JOIN project
ON project.of_dpt=department.dpt_name
LEFT JOIN employee
ON employee.in_dpt=department.dpt_name
GROUP BY department.dpt_name;
显示的结果:
mysql> SELECT department.dpt_name,
-> COUNT(DISTINCT project.proj_name) AS 'count_project',
-> COUNT(DISTINCT employee.name) AS 'count_people'
-> FROM department
-> LEFT JOIN project
-> ON project.of_dpt=department.dpt_name
-> LEFT JOIN employee
-> ON employee.in_dpt=department.dpt_name
-> GROUP BY department.dpt_name
-> ;
+----------+---------------+--------------+
| dpt_name | count_project | count_people |
+----------+---------------+--------------+
| dpt1 | 1 | 3 |
| dpt2 | 2 | 4 |
| dpt3 | 1 | 3 |
| dpt4 | 2 | 2 |
+----------+---------------+--------------+
4 rows in set (0.00 sec)
继续加油咯!