SQL 报表制作和整形( 四 ) _生活百科

文章插图
（2）因为需要去除空值，把 Dept20 和 Dept30 的数据移上去。使用窗函数 row_number，并且分组。
select rn,max(Dept10) Dept10Count,max(Dept20) Dept20Count,max(Dept30)Dept30Countfrom (SELECT row_number() over(partition by DeptNo) rn,case DeptNo when 10 then '*' else ''end as Dept10,case DeptNo when 20 then '*' else ''end as Dept20,case DeptNo when 30 then '*' else ''end as Dept30FROM test.emps order by DeptNo ) a group by rn

文章插图
（3）最后根据编号倒序排序即可完成。
select rn,max(Dept10) Dept10Count,max(Dept20) Dept20Count,max(Dept30)Dept30Countfrom (SELECT row_number() over(partition by DeptNo) rn,case DeptNo when 10 then '*' else ''end as Dept10,case DeptNo when 20 then '*' else ''end as Dept20,case DeptNo when 30 then '*' else ''end as Dept30FROM test.emps order by DeptNo ) a group by rn order by rn desc

文章插图
11.返回未被作用分组依据的列
返回未包含在 Group By 子句中的列，标准SQL是不允许的。因为未被作用分组依据的列在各行中不是唯一的。
情景：找出各部门中薪水最高和最低的员工，以及每个角色中薪水最高和最低的员工。并显示每个员工的名字、部门、角色和薪水。如下：

文章插图
解决方案：使用窗函数 max over 和 min over 返回相应部门和角色的最高和最低薪水作为子结果集。然后只保留等于这些薪水的员工。
select Ename,DeptNo,Role,SAL,case SAL when max_by_DeptNo then '部门最高'when min_by_DeptNo then '部门最低'end '部门薪水',case SAL when max_by_Role then '角色最高'when min_by_Role then '角色最低'end '角色薪水' from (SELECT Ename,DeptNo,Role,SAL,max(SAL) over(partition by DeptNo) max_by_DeptNo,min(SAL) over(partition by DeptNo) min_by_DeptNo,max(SAL) over(partition by Role) max_by_Role,min(SAL) over(partition by Role)as min_by_Role FROM test.emps ) a where SAL in(max_by_DeptNo,min_by_DeptNo,max_by_Role,min_by_Role) ;保留相应薪水员工使用了 in 查询 where SAL in(max_by_DeptNo,min_by_DeptNo,max_by_Role,min_by_Role)。
12.计算简单的小计
返回一个结果集，其中包含小计（聚合分组的特定列）和总计（聚合整张表的特定列）。
情景：返回每种角色的薪水总额，以及整张表的所有薪水总额。
解决方案：可以使用 group by 子句的 rollup 扩展。rollup 表示汇总。
SELECT COALESCE(Role,'总计') 角色,sum(SAL) 薪水 FROM test.emps group by Role with rollup;

文章插图
13.计算各种可能的小计
情景：找出不同部门、角色、部门/角色组合的薪水小计，同时显示整个员工表的薪水总计。

文章插图
解决方案：使用 group by 子句的 cube 扩展,以及 grouping 函数（MySQL 不支持，这里使用 SqlServer 演示）。
select * from (SELECTcase grouping([DeptNo]) when 0 then [DeptNo] else '全部' end as 部门,case grouping([Role]) when 0 then [Role] else '全部' end as 角色,sum(SAL) 薪水总额FROM [yesmro_db].[dbo].[Emps] group by [DeptNo],[Role] with cube ) aorder by 部门,角色......
未完待续
【SQL 报表制作和整形】