企业官网建设流程全解析

# SQL Server分组聚合、多表查询作业笔记 > 数据库：`SararyDB` > 表：`Emp`员工表、`Dept`部门表 > 重点：聚合函数、`where`与`having`区别、`group by`、多表`join`、子查询、`count`三种写法辨析 ## 一、表结构回顾 ### Emp 员工表 |字段|说明| |---|---| |EMPNO|员工号(主键)| |ENAME|员工姓名| |ESex|性别| |JOB|职位| |MGR|直接领导员工号| |HIREDATE|出生日期| |SAL|工资 money类型| |DEPTNO|所属部门号(外键关联Dept)| ### Dept 部门表 |字段|说明| |---|---| |DEPTNO|部门号(主键)| |DNAME|部门名称| |LOCTION|地址| ## 二、聚合函数核心知识点 1. `count()`：统计行数 - `count(*)`：统计全部行，**包含NULL行**，标准SQL写法 - `count(1)`：常量1统计行数，SQL Server下性能与`count(*)`完全一致 - `count(列名)`：**忽略该字段为NULL的行**，只统计字段非空数据 2. `sum(列)`：求和 3. `avg(列)`：求平均值 4. `max(列)`：最大值 5. `min(列)`：最小值 > 易错：`count(列名)`会过滤null；`count(*)` / `count(1)`不会过滤null。 示例代码 ```sql --统计男员工人数 count(*) / count(1)均可 SELECT count(*) AS 男职员人数 FROM Emp WHERE ESex='男'; SELECT count(1) AS 男职员人数 FROM Emp WHERE ESex='男';

三、where 和 having 必考区分

• where：group by 分组之前过滤原始表数据，不能写聚合函数（avg/max/sum）
• having：group by 分组完成之后，过滤分组结果，可以写聚合函数

❌错误示例

--错误：where里面不能直接使用avg聚合函数SELECTDEPTNO,avg(SAL)AS平均工资FROMEmpWHEREavg(SAL)<2000GROUPBYDEPTNO;

✅正确示例

--13题：部门平均工资低于2000SELECTDEPTNO,avg(SAL)AS平均工资FROMEmpGROUPBYDEPTNOHAVINGavg(SAL)<2000;

四、group by 使用规则

select 后面非聚合的字段，必须全部写在 group by 后面。

✅正确

SELECTDEPTNO,JOB,count(1)FROMEmpGROUPBYDEPTNO,JOB;

❌错误

--DEPTNO出现在select，没有写进group bySELECTDEPTNO,JOB,count(1)FROMEmpGROUPBYJOB;

五、多表连接 inner join /left join

1. innerjoin

   （简写 join）：

   两边匹配上的数据才输出

   • 坑：如果某个部门没有任何员工，该部门直接消失，结果看不到。

2. leftjoin

   ：以左边表全部保留，右边匹配不到填充 NULL

   • 需要展示**全部部门（包括无员工部门）**优先使用 left join。

left join 统计人数：不能写count(1)，要写count(主键列)，否则空数据会错误统计为 1。

示例 1 inner join（第 11 题原始写法）

--只展示有员工的部门，输出部门名字SELECTd.Dname,count(1)AS每部门人数FROMEmp eJOINDept dONe.DeptNo=d.DeptNoGROUPBYd.Dname;

示例 2 left join（完整版本，推荐考试）

--全部部门都展示，无员工部门人数显示0SELECTd.Dname,count(e.EMPNO)AS每部门人数FROMDept dLEFTJOINEmp eONd.DeptNo=e.DeptNoGROUPBYd.DEPTNO,d.Dname;

⚠高频坑：区分【职位】和【部门】

• JOB='销售'：员工岗位职位叫销售（Emp 表字段）
• DNAME='销售部'：部门名称叫销售部（Dept 表字段） ❌不要用where Job='销售'去筛选销售部员工，完全两码事！

✅第 3 题统计销售部员工

SELECTcount(1)AS销售部人数FROMEmp eJOINDept dONe.DEPTNO=d.DEPTNOWHEREd.DNAME='销售部';

✅第 4 题统计调查部、业务营运部

SELECTcount(1)AS总人数FROMEmp eJOINDept dONe.DEPTNO=d.DEPTNOWHEREd.DNAMEIN('调查部','业务营运部');

六、子查询使用

第 7 题：查询 BLAKE 的下属。MGR 字段存储领导的员工号。 逻辑：先查出 BLAKE 的员工号，再查询哪些员工 MGR 等于这个编号。

✅正确

SELECTcount(1)ASBlake下属人数FROMEmpWHEREMGR=(SELECTEMPNOFROMEmpWHEREENAME='BLAKE');

❌易错坑：不要统计 BLAKE 本人，不要写where ENAME='BLAKE'做 count。

七、日期函数 month ()

SQL Servermonth(日期字段)，提取月份数字。 第 6 题：统计 1 月份出生员工

SELECTcount(1)AS一月出生人数FROMEmpWHEREMONTH(HIREDATE)=1;

八、全部作业完整可运行代码

USESararyDB;GO--1.统计男职员的人数SELECTcount(1)AS男职员人数FROMEmpWHEREESex='男';--2.统计女职员的人数SELECTcount(1)AS女职员人数FROMEmpWHEREESex='女';--3.统计"销售部"员工人员SELECTcount(1)AS销售部人数FROMEmpJOINDeptONEmp.DEPTNO=Dept.DEPTNOWHEREDNAME='销售部';--4.统计"调查部"和"业务营运部"的员工人数SELECTcount(1)AS总人数FROMEmpJOINDeptONEmp.DEPTNO=Dept.DEPTNOWHEREDNAMEIN('调查部','业务营运部');--5.统计“经理”的人数SELECTcount(1)AS经理人数FROMEmpWHEREJOB='经理';--6.查询是1月份出生的员工的人数SELECTcount(1)AS一月出生人数FROMEmpWHEREMONTH(HIREDATE)=1;--7.查询"BLAKE"的下属的人数SELECTcount(1)ASBlake下属人数FROMEmpWHEREMGR=(SELECTEMPNOFROMEmpWHEREENAME='BLAKE');--8.查询所有员工的平均工资SELECTavg(SAL)AS平均工资FROMEmp;--9.查询所有员工的工资和SELECTsum(SAL)AS工资总和FROMEmp;--10.查询员工的最高工资SELECTmax(SAL)AS最高工资FROMEmp;--11.统计每个部门的人数 inner join版本(只显示有员工的部门)SELECTd.Dname,count(1)AS每部门人数FROMEmp eJOINDept dONe.DeptNo=d.DeptNoGROUPBYd.Dname;--11拓展 left join全部部门版本（推荐考试）SELECTd.Dname,count(e.EMPNO)AS每部门人数FROMDept dLEFTJOINEmp eONd.DeptNo=e.DeptNoGROUPBYd.DEPTNO,d.Dname;--12.统计每个部门的平均工资SELECTd.Dname,avg(e.SAL)AS平均工资FROMEmp eJOINDept dONe.DeptNo=d.DeptNoGROUPBYd.Dname;--13.统计部门平均工资低于2000元部门信息，显示(部门号，平均工资)SELECTDEPTNO,avg(SAL)AS平均工资FROMEmpGROUPBYDEPTNOHAVINGavg(SAL)<2000;--14.统计每个部门的最高工资SELECTDEPTNO,max(SAL)AS最高工资FROMEmpGROUPBYDEPTNO;--15.统计每个部门的最低工资SELECTDEPTNO,min(SAL)AS最低工资FROMEmpGROUPBYDEPTNO;--16.统计每个部门的最高工资，最低工资SELECTDEPTNOAS部门号,max(SAL)AS最大值,min(SAL)AS最小值FROMEmpGROUPBYDEPTNO;--17.统计每个部门的最高工资与最低工资之和小于3000的信息SELECTDEPTNOAS部门号,max(SAL)AS最大值,min(SAL)AS最小值FROMEmpGROUPBYDEPTNOHAVINGmax(SAL)+min(SAL)<3000;--18.统计不同职位的在岗人数SELECTJOBAS职位,count(*)AS职员人数FROMEmpGROUPBYJOB;--19.统计不同职位的工资和SELECTJOBAS职位,sum(SAL)AS工资和FROMEmpGROUPBYJOB;

九、易错点速查表

表格