SQL 报表制作和整形

　　本文章主要介绍制作报表的查询，这些查询通常需要考虑与报表相关的格式设置，还需使用多级聚合。

 

1.将结果集转置为一行（行转列）

　　将多行中的值转换为单行中的列。

　　情景：有一个员工表，统计出一个结果集，显示了每个部门的员工数量，如下图一。现在需要调整输出格式，显示成一行，如图二。

　　  

 

　　　　　　　　　　　　　　图一

 

　　

 

 　　　　　　　　　图二

 

　　解决方案：使用 case 表达式和 SUM 聚合函数来转置结果集。（Sqlserver 可以使用转置函数 PIVOT 实现，这里不讨论，可以参考官方文档）

　　使用 case 表达式将行拆分成列，并且标记每行数据是否属于这个部门。然后，由于这里的问题是计算每个部门的员工数量，因此使用 SUM 聚合函数。

 select 
sum(case DeptNo when 10 then 1 else 0 end) as DeptNo_10, 
sum(case DeptNo when 20 then 1 else 0 end)  as DeptNo_20,
sum(case DeptNo when 30 then 1 else 0 end) as DeptNo_30
 from test.emps;

　　

　　拆解：

　　　　（1）第一步将行转换为列，并标记每行数据属于哪个部门。

　　　　

 

 　　　　（2）第二步是计算每个部门的人数，根据DeptNo分组。第二步属于过渡步骤，熟练了可以直接跳过。

　　　　　　

 

 

　　　　（3）第三步，目标是返回一行数据，所以删除 DeptNo 和 group by 即可。

　　　　　　

 

 

　　也可以使用另一种写法，先分组统计出每个部门的人数，然后再行转列。

 select 
    sum(case DeptNo when 10 then cnt else 0 end )  as DeptNo_10 ,
    sum(case DeptNo when 20 then cnt else 0 end )  as DeptNo_20 ,
    sum(case DeptNo when 30 then cnt else 0 end )  as DeptNo_30 
 from (
 select DeptNo,count(*) cnt from  test.emps group by DeptNo) a ;

　　

 

 

2.将结果集转置为多行

　　通过为给定列中每个不同的值都创建一列，也是行转列。不同的是要输出多行。

　　情景：图一是每个员工及其角色。想让每个角色为一列，每列下面为是该角色的员工名称，如图二。

　　　　　　　　　　　　

 　　　　　　　　图一　　　　　　　　　　　　　　　　　　　　图二

 　　解决方案：

　　　　该情景不同于上一个情景，这次需要返回多行，所以不能按照角色分组然后使用聚合函数。要解决这个问题，必须让每个 角色/员工名 组合是独一无二。可以使用窗函数 row_number() over(partition by 角色 order by 员工名)  给每个组合做编号。然后再使用 case 表达式和聚合函数 Max 对结果进行转置，最后根据窗函数做的编号进行分组。

  SELECT 
max(case Role when 'PPS' then EName else '' end) as PPS, 
Max(case Role when 'PM' then EName else '' end)  as PM,
max(case Role when 'BD' then EName else '' end) as BD,
max(case Role when 'CS' then EName else '' end) as CS
from (
 select  Role,EName, row_number() over(partition by Role order by EName) rn  FROM test.emps) a
 group by rn
 

　　

　　拆解：

　　　　（1）图一是按照上个解决方案查询出的结果。虽然给每个角色显示了每一列，也返回了多行，但是中间存在间隙。所以不能直接转置，需要先给每个 角色/员工名 组合做编号，如图二。

　　　　　　　　　　　　　　

 

 

 　　　　　　　　　　　　　　　　图一　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　图二

　　　　（2）现在根据上述结果集进行转置。

  SELECT rn,
case Role when 'PPS' then EName else '' end as PPS, 
case Role when 'PM' then EName else '' end  as PM,
case Role when 'BD' then EName else '' end as BD,
case Role when 'CS' then EName else '' end as CS
from (
 select  Role,EName, row_number() over(partition by Role order by EName) rn  FROM test.emps) a;

　　　　　　

 

 　　　　（3）最后要做的就是删除空值，消除间隙。只需要按照编号 rn 分组然后使用 MAX 聚合函数即可解决。

  SELECT rn,
max(case Role when 'PPS' then EName else '' end) as PPS, 
Max(case Role when 'PM' then EName else '' end)  as PM,
max(case Role when 'BD' then EName else '' end) as BD,
max(case Role when 'CS' then EName else '' end) as CS
from (
 select  Role,EName, row_number() over(partition by Role order by EName) rn  FROM test.emps) a
 group by rn

　　

 

3.对结果集进行逆转置（列转行）

　　 情景：将第一个情景中的结果集转换为多行。

　　 　　　　转换为  　　

 

 

　　解决方案：需要一个透视表，然后使用笛卡尔积。

　　需要事先知道转换为行的行数，就是列数。生成一个该行数的透视表，然后进行关联。再使用 case 表达式选择其中一列。

　　这里生成透视表使用递归生成，也可以从员工表查询去重部门编号的结果集作为透视表。

with recursive t3 as
(
    select 1 as id 
    union all
    select id+1 as id from t3
    where id < 3
)


select id*10 as DeptNo,
    case id 
        when 1 then DeptNo_10
        when 2 then DeptNo_20
        when 3 then DeptNo_30
        end as Count
 from t3
join deptcounts a ;

　　

 

 

4.将结果集逆转置为一列

　　将查询返回的所有列都放在一列中，并返回它们。　　

　　情景：返回10号部门所有员工的名字、角色和薪水，并将这三个值放在一列中。并在员工之间添加一行。如下：

　　　　

 

 　　解决方案：由结果可以看出，每个员工需要返回四行，由此我们需要一张包含四行数据的透视表（使用 CTE）进行笛卡尔积。然后使用 case 表达式将三列转换为一列。

with recursive t4 as
(
    select 1 as id 
    union all 
    select id +1 as id from t4
    where id < 4
)

/*
 select t4.id,a.EName,a.SAL,a.Role from  test.emps a
join t4
where a.DeptNo = 10
order by Ename ;
*/

select
    case t4.id
        when 1 then EName
        when 2 then Role
        when 3 then SAL
        when 4 then ''
        end as EMPS
 from  test.emps a
join t4
where a.DeptNo = 10
order by Ename

 

5.消除结果集中的重复值

　　在制作报表时，出现多行的同一列的值相同，需要这个列值只显示一次。

　　情景：从员工表返回部门编号和员工名字并按部门编号分组，对于每个部门编号只需显示一次。如下：

　　　　

 

 　　解决方案：使用窗函数 Lag over 返回当前数据前一行的部门编号，并与当前数据的部门编号进行比较。如果相同就显示空值，即与前一行数据属于同一部门；如果不同就显示当前数据的部门编号，即当前数据是下一个部门数据的第一条数据。

SELECT case  when  lag(DeptNo) over(order by DeptNo)  = DeptNo then '' else DeptNo  end 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_10 
from (
select 
sum(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;

　　

 

8.创建预定数量的桶数

　　将数据划分到数量固定的几个桶中。这是一种组织分类数据的常见方式，因为在很多分析中，将一个集合分成多个规模相同的集合是第一步。

　　情景：将员工表中的数据划分到3个组内。如下：

　　　　

 

　　 解决方案：

　　　　1.使用窗函数 ntile ，ntile 会将一个集合划分到指定数量的桶中。如果无法均分，就将多出来的元素放到前面的捅中。

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)  Dept30Count
from (
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 Dept30
  FROM test.emps order by DeptNo ) a group by rn order by rn desc

 

　　分拆：

　　　　（1）行转列，且替换字符串：

SELECT 
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 Dept30
  FROM test.emps order by DeptNo

　　

 

 　　（2）因为需要去除空值，把 Dept20 和 Dept30 的数据移上去。使用窗函数 row_number ，并且分组。

select rn,max(Dept10) Dept10Count,max(Dept20) Dept20Count,max(Dept30)  Dept30Count
from (
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 Dept30
  FROM test.emps order by DeptNo ) a group by rn 

　　

 

 　　（3）最后根据编号倒序排序即可完成。

select rn,max(Dept10) Dept10Count,max(Dept20) Dept20Count,max(Dept30)  Dept30Count
from (
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 Dept30
  FROM 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 (
  SELECT 
  case 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 ) a
  order by 部门,角色