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

SELECT EName,ntile(3) over() 组号 FROM test.emps;2.另一种方法是，对数据进行分组。按顺序将数据放到三个桶中，先将数据编号，然后取余数，余数即组号。最后按照组号排序。
SELECT EName,((row_number() over()) % 3 )+ 1 组号,row_number() over()编号,(row_number() over()) % 3 余数 FROM test.emps order by 组号注意：根据上一个情景和本次情景找到规律。将一个集合划分到固定尺寸的组中时使用求商数，将集合划分到固定组数时使用求余数。
9.创建水平直方图
情景：创建沿水平方向延伸的直方图。以水平直方图的方式显示每个角色的员工数量，在直方图中每个星号表示一个员工。

文章插图
解决方案：方案的关键是，将统计后的数字用 * 字符的形式展示。可以使用字符串函数 lpad 填充生成对应数量的字符串。
SELECT Role,lpad('*',count(*),'*') 数量 FROM test.emps group by Role; 10.创建垂直直方图
情景：以垂直直方图的方式显示每个部门的员工数量，如下：

文章插图
解决方案：从最终结果集看出，首先需要行转列，然后替换字符串。最关键的是需要是按照部门编号分区分组编号，再根据这个编号分组去除空值。
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【SQL 报表制作和整形】分拆：
?。?）行转列，且替换字符串：
SELECTcase 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

文章插图
　?。?）因为需要去除空值，把 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

文章插图
　?。?）最后根据编号倒序排序即可完成。
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 表示汇总。