MySQL -- 使用 select/join/count() 从表中提取信息 [英] MySQL -- extracting information from table with select/join/count()

问题描述

我正在做一项 MySQL 作业，我应该做的一件事是

显示部门代码以及分配给该部门的员工人数."

I'm working on a MySQL homework assignment, and one thing I am supposed to do is

"Display the department code along with the number of employees that are assigned to that department."

我尝试了许多不同的命令，包括下面列出的命令，但我似乎无法根据下表获得正确的结果.

I've tried many different commands, including the command listed below, but I just can't seem to get the correct result based on the tables below.

关于如何获取请求的子表的任何想法?我已经给出了下面的相关表格.

Any ideas on how to get the requested sub-table? I've given the relevant tables below.

谢谢.

select code,count(distinct employee) 
from (department 
left outer join Assignment on department.code = assignment.department);

部门

说明|代码|姓名|主管|建筑|
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Department

Description| Code| Name| Supervisor| building|
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

文字" |OPR |经营 |77881234 |区块B|
文本" |电磁场 |紧急|54546677|区块A|
文本" |耐多药|病历|99778525|区块A|

"text" | OPR | Operating | 77881234 | BlockB|
"text" | EMM | Emergency | 54546677| BlockA|
"text" | MDR | Medical record| 99778525| BlockA|

分配

员工|部门 |分配日期|角色|
+++++++++++++++++++++++++++++++++++++++++++++++++++
54546677 |电磁场 |01/15/2011 |主管|
54546677 |电磁场 |02/25/2003|医生|
77881234 |OPR |02/15/2010 |主管|
77881234|OPR |01/20/2000|医生|
77881234 |电磁场 |02/20/1999 |医生|

ASSIGNMENT

Employee| Department | DateAssigned| Role|
+++++++++++++++++++++++++++++++++++++++++++++++++++
54546677 | EMM | 01/15/2011 | Supervisor|
54546677 | EMM | 02/25/2003| Doctor|
77881234 | OPR | 02/15/2010 | Supervisor|
77881234| OPR | 01/20/2000| Doctor|
77881234 | EMM | 02/20/1999 | Doctor|

推荐答案

您是否尝试过使用 group by 子句?

have you try using group by clause?

这篇关于MySQL -- 使用 select/join/count() 从表中提取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！