SQL 報表製作和整形 - tw511教學網

　　本文章主要介紹製作報表的查詢，這些查詢通常需要考慮與報表相關的格式設定，還需使用多級聚合。

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 部門,角色

......

未完待續