固定行轉列比較容易實現.
但是動態行轉列的需求,用SQL都難以實現,要不然就是超級複雜。
不過很多第三方工具都已經提供了很好的支援,不一定非得用SQL實現.比如帆軟報表和Excel。

最近接到一個比較複雜的查詢,並且要求動態行轉列.

查詢的SQL如下

select type,ts,cn,cast(val as DECIMAL) val from (
select
starttime,'釋出' type,base.ts,base.cn,ifnull(t1.val,0) val
from
(
select
t1.*,
startdate+ interval id-1 hour starttime,
startdate+ interval id hour endtime,
date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
vars.*
from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
(
select 'mvbox_user.user_otherinfo' busidatatype,1 type,'內容稽核.個人資料' cn union all
select 'mvbox_user.user_baseinfo',1,'內容稽核.個人喜好' union all
select 'photo_album',1,'內容稽核.相簿資訊' union all
select 'photo_list',1,'內容稽核.圖片資訊' union all
select 'music_original',1,'內容稽核.原唱資訊' union all
select 'music_cover',1,'內容稽核.翻唱資訊' union all
select 'music_accompany',1,'內容稽核.伴奏資訊' union all
select 'music_album',1,'內容稽核.音樂專輯' union all
select 'music_video',1,'內容稽核.影片資訊' union all
select 'blog_album',1,'內容稽核.日誌與文章' union all
select 'mvbox_user.user_baseinfo',2,'內容稽核.MVBOX頭像稽核'
) t1
where id<= TIMESTAMPDIFF(hour,startdate,enddate)
order by busidatatype,type,starttime
) base left join
(
select busidatatype,type,date_format(createtime,'%m%d%H') ts,count(*) val from audit_obj_detail
where createtime>=${starttime} and createtime<${endtime}
and busitype = 'mvbox'
group by busidatatype,type,date_format(createtime,'%m%d%H')
) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
group by base.busidatatype,base.type,base.ts,base.cn
union all
select
starttime, '稽核' type,base.ts,base.cn,ifnull(t1.val,0) val
from
(
select
t1.*,
startdate+ interval id-1 hour starttime,
startdate+ interval id hour endtime,
date_format(startdate+ interval id-1 hour,'%m%d%H') ts,
vars.*
from nums ,(select ${starttime} startdate,${endtime} enddate) vars,
(
select 'mvbox_user.user_otherinfo' busidatatype,1 type,'內容稽核.個人資料' cn union all
select 'mvbox_user.user_baseinfo',1,'內容稽核.個人喜好' union all
select 'photo_album',1,'內容稽核.相簿資訊' union all
select 'photo_list',1,'內容稽核.圖片資訊' union all
select 'music_original',1,'內容稽核.原唱資訊' union all
select 'music_cover',1,'內容稽核.翻唱資訊' union all
select 'music_accompany',1,'內容稽核.伴奏資訊' union all
select 'music_album',1,'內容稽核.音樂專輯' union all
select 'music_video',1,'內容稽核.影片資訊' union all
select 'blog_album',1,'內容稽核.日誌與文章' union all
select 'mvbox_user.user_baseinfo',2,'內容稽核.MVBOX頭像稽核'
) t1
where id<= TIMESTAMPDIFF(hour,startdate,enddate)
order by busidatatype,type,starttime
) base left join
(
select busidatatype,type,date_format(AuditTime,'%m%d%H') ts,count(*) val from audit_obj_detail
where AuditTime>=${starttime} and AuditTime<${endtime}
and busitype = 'mvbox'
group by busidatatype,type,date_format(AuditTime,'%m%d%H')
) t1 on (base.busidatatype=t1.busidatatype and base.type=t1.type and base.ts=t1.ts)
group by base.busidatatype,base.type,base.ts,base.cn
) t1 ;

由於這個SQL已然比較複雜，再加動態行轉列,可讀性幾乎就沒有了.
這個SQL查詢的結果大致如下。

其中type可能是釋出或者稽核.
ts 表示月日和小時
cn表示模組型別
val表示數量.

將這個結果匯入至Excel
選擇插入,資料透視表

然後將ts 設定為列標籤,這樣 ts 就由行變成列顯示
cn和type作為行標籤.
val作為顯示數值

然後選擇檢視->凍結窗格->凍結首列方便觀看資料

可以看到如下結果

完全符合動態行轉列的需求,並且可以排序和篩選.太強大了.

Excel實現動態行轉列(資料透視表)

相關文章