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

文章插图
3.对结果集进行逆转置（列转行）
情景：将第一个情景中的结果集转换为多行。

文章插图
转换为

文章插图
解决方案：需要一个透视表，然后使用笛卡尔积。
需要事先知道转换为行的行数，就是列数。生成一个该行数的透视表，然后进行关联。再使用 case 表达式选择其中一列。
这里生成透视表使用递归生成，也可以从员工表查询去重部门编号的结果集作为透视表。
with recursive t3 as(select 1 as idunion allselect id+1 as id from t3where id < 3)select id*10 as DeptNo,case idwhen 1 then DeptNo_10when 2 then DeptNo_20when 3 then DeptNo_30end as Count from t3join deptcounts a ;

文章插图
4.将结果集逆转置为一列
将查询返回的所有列都放在一列中，并返回它们。
情景：返回10号部门所有员工的名字、角色和薪水，并将这三个值放在一列中。并在员工之间添加一行。如下：

文章插图
解决方案：由结果可以看出，每个员工需要返回四行，由此我们需要一张包含四行数据的透视表（使用 CTE）进行笛卡尔积。然后使用 case 表达式将三列转换为一列。
with recursive t4 as(select 1 as idunion allselect id +1 as id from t4where id < 4)/* select t4.id,a.EName,a.SAL,a.Role fromtest.emps ajoin t4where a.DeptNo = 10order by Ename ;*/selectcase t4.idwhen 1 then ENamewhen 2 then Rolewhen 3 then SALwhen 4 then ''end as EMPS fromtest.emps ajoin t4where a.DeptNo = 10order by Ename5.消除结果集中的重复值
在制作报表时，出现多行的同一列的值相同，需要这个列值只显示一次。
情景：从员工表返回部门编号和员工名字并按部门编号分组，对于每个部门编号只需显示一次。如下：

文章插图
解决方案：使用窗函数 Lag over 返回当前数据前一行的部门编号，并与当前数据的部门编号进行比较。如果相同就显示空值，即与前一行数据属于同一部门；如果不同就显示当前数据的部门编号，即当前数据是下一个部门数据的第一条数据。
SELECT casewhenlag(DeptNo) over(order by DeptNo)= DeptNo then '' else DeptNoend as DeptNo,EName FROM test.emps;6.转置结果集以简化涉及多行的计算
要执行的计算涉及多行的数据，为简化工作，你想要将这些行转置为列，这样你需要的所有数据都会出现在同一行中。
情景：薪水总额最高的部门是10号，如图一。想要计算20号部门和30号部门的薪水总额分别比10号部门少多少。最终结果如图二：

文章插图

文章插图
图一图二
解决方案：通过 SUM 聚合函数和 Case 表达式，先将各部门薪水总额转置成一行，然后作为子结果集进行运算。
select DeptNo_10-DeptNo_20 as diff_20_10,DeptNo_10-DeptNo_30 as diff_30_10from (selectsum(case DeptNo when 10 then SAL end) as DeptNo_10, sum(case DeptNo when 20 then SAL end) as DeptNo_20,sum(case DeptNo when 30 then SAL end) as DeptNo_30 from test.emps ) a 7.创建尺寸固定的数据桶
情景：基于员工表中的员工进行分，每组包含5位员工。最终结果集如下图：

文章插图
解决方案：主要要解决的问题是将数据分组，所以要给数据编号，然后划分组。
使用排名函数 row_number 进行排名，然后执行除法运算并将商向上取整，最后的值既是组号。
SELECT row_number() over() 排名,row_number() over() / 5.0 商, ceil(row_number() over() / 5.0) 组号,EName FROM test.emps;