SQL交叉表

之前做貨品橫向展示時，有看到評論說用到交叉表。

公司最近需要給訂單表做一個數據彙總的功能，同事給到一個參考SQL

select * from (select COUNT(1) as 已鎖定 from tbl_order where orderLock = 1) as A,
(select COUNT(1) as 未鎖定 from tbl_order where orderLock = 0) as B,
(select COUNT(1) as 未發貨 from tbl_order where PushStatus=1 and statusid=14) as C,
(select COUNT(1) as 待發貨 from tbl_order where PushStatus=1 and statusid=14 and isshipments=1) as D,
(select COUNT(1) as 已發貨 from tbl_order where statusid=4) as E,
(select COUNT(1) as 未分配 from tbl_order where statusid = 14 and PushStatus<>3 and pushstatus=0) as F,
(select COUNT(1) as 已分配 from tbl_order where PushStatus=1) as G,
(select COUNT(1) as 已推送 from tbl_order where PushStatus=2) as H

看完改為下麵SQL

SELECT
COUNT(CASE WHEN orderLock=1 THEN 1 ELSE NULL END) AS '已鎖定',
COUNT(CASE WHEN orderLock=0 THEN 1 ELSE NULL END) AS '未鎖定',
COUNT(CASE WHEN PushStatus=1 and statusid=14 THEN 1 ELSE NULL END) AS '未發貨',
COUNT(CASE WHEN PushStatus=1 and statusid=14 and isshipments=1 THEN 1 ELSE NULL END) AS '待發貨',
COUNT(CASE WHEN statusid=4 THEN 1 ELSE NULL END) AS '已發貨',
COUNT(CASE WHEN statusid = 14 and PushStatus<>3 and pushstatus=0 THEN 1 ELSE NULL END) AS '未分配',
COUNT(CASE WHEN PushStatus=1 THEN 1 ELSE NULL END) AS '已分配',
COUNT(CASE WHEN PushStatus=2 THEN 1 ELSE NULL END) AS '已推送'
FROM tbl_order

與上面參考SQL相比只用到了一次查詢表，並且如果有where條件更利於擴展。

直接上圖 SQL執行計劃參考：

 這個差距顯而易見，歡迎大家提出更加優化的SQL。