SQL统计部门人数,人数为0的部门也要显示出来
相同点:它们都以一张部门表(或类别表),其它表都有部门编号DepartmentID(类别编号)
案例一:一张表
select c.DepartmentID,c.DepartmentName, t.Num AS '人员数量' from T_Department as c
left join (select DepartmentID,COUNT(*) AS Num from T_User group by DepartmentID) as t on c.DepartmentID= t.DepartmentID
核心思想就是: 拆分
1将人员表按部门编号统计数量: ( select DepartmentID,COUNT(*) AS Num from T_User group by CompanyID) 得到t: 部门编号、部门人员数量;
2将 部门表与 t ,根据部门编号联表左连接,得到 :部门名称、部门人员数量
案例二:多张表
统计提交的 调查人员、设备、规范、数据来源规范 表的数量
select c.CompanyID,c.CompanyName, t1.Num AS '调查人员数量',t2.Num AS '设备数量',t.Num AS '规范数量',t3.Num AS '数据来源规范数量' from T_Company as c
left join (select CompanyID,COUNT(*) AS Num from T_Standard as s where s.HasSubmitted=1 group by CompanyID) as t on c.CompanyID= t.CompanyID
left join (select CompanyID,COUNT(*) AS Num from T_Investigator as s where s.HasSubmitted=1 group by CompanyID) as t1 on c.CompanyID= t1.CompanyID
left join (select CompanyID,COUNT(*) AS Num from T_Machine as s where s.HasSubmitted=1 group by CompanyID) as t2 on c.CompanyID= t2.CompanyID
left join (select CompanyID,COUNT(*) AS Num from T_DataSource as s where s.HasSubmitted=1 group by CompanyID) as t3 on c.CompanyID= t3.CompanyID
order by c.CompanyID
最后赋值粘贴到Excel,在Excel搞个合计:
最新文章
- ubuntu安装vim时提示 没有可用的软件包 vim,但是它被其它的软件包引用了 解决办法
- TCL笔试题 将A,B,B,C,D,E,第三个字符不可以是E的所有组合输出;
- Codeforces Round #371 (Div. 2)(set\unique)
- C语言中常量
- [SoapUI] JDBC 请求连接SQL Sever,MySQL
- dplyr 数据操作 常用函数(5)
- Struts-ValueStack和OGNL总结
- dns-prefetch,新打开页面预抓取
- MarkDown学习——基础用法
- BZOJ1800:fly 飞行棋 (双指针 组合数)
- linux学习笔记-文件相关知识
- 《mysql从入门到精通》提高
- Initialization of bean failed; nested exception is java.lang.IllegalArgumentException: error at ::0 inconsistent binding
- .NET Core开发日志——RequestDelegate
- C/C++中的位运算
- HDU 1716 排列2 (格式问题+排列)
- mysql-5.7.12-winx64 安装
- angularJS1笔记-(10)-自定义指令(templateUrl属性)
- 用 Hystrix 构建高可用服务架构
- python中的上下文管理器