1卫病、透視原理:就是將查詢結(jié)果進行轉(zhuǎn)置
下面就舉例來說明:
執(zhí)行下面語句:檢查是否含有表 dbo.Orders待榔,如果有就將表刪除:
if OBJECT_ID('dbo.Orders','U') is not null2
drop table dbo.Orders
然后創(chuàng)建表dbo.Orders:
create table dbo.Orders
(
orderid int not null primary key,
empid int not null,
custid int not null,
orderdate datetime,
qty int
)
批量插入數(shù)據(jù):
insert into dbo.Orders (orderid,orderdate,empid,custid,qty) values
(30001,'20070802',3,1,10),
(10001,'20071224',2,1,12),
(10005,'20071224',1,2,20),
(40001,'20080109',2,3,40),
(20001,'20080212',2,2,12),
(10006,'20080118',1,3,14),
(40005,'20090212',3,1,10),
(20002,'20090216',1,3,20),
(30003,'20090418',2,2,15),
(30004,'20070418',3,3,22),
(30007,'20090907',3,4,30)
業(yè)務邏輯:查詢出 每個 員工 處理的 每個客戶的 訂單總數(shù)
普通的查詢方式:
select
empid,custid,SUM(qty)
as sumqtyfrom
Ordersgroup by empid,
custidorder by empid
查詢結(jié)果:
但是現(xiàn)在想要的結(jié)果是:
其中的A、B坟岔、C分別代表三個 客戶Id帝簇,需要將原來的結(jié)果進行轉(zhuǎn)置笑诅。
實現(xiàn)上面的結(jié)果就是sql里面的透視:
三個步驟:
- 1、將結(jié)果數(shù)據(jù)進行分組
- 2宣决、將結(jié)果數(shù)據(jù)進行擴展
- 3蘸劈、將結(jié)果數(shù)據(jù)進行聚合
第一種是實現(xiàn)方式:復雜、簡單易懂的方式:使用相關(guān)子查詢:
select
empid,
--下面是相關(guān)子查詢尊沸,不是表的連接
(
select
SUM(qty)
from Orders as innerO
where innerO.empid=outerO.empid and custid=1
group by innerO.empid
) as A ,
(
select
SUM(qty)
from Orders as innerO
where innerO.empid=outerO.empid and custid=2
group by innerO.empid
) as B ,
(
select
SUM(qty)
from Orders as innerO
where innerO.empid=outerO.empid and custid=3
group by innerO.empid
) as C
from Orders as outerO
group by empid
第二種實現(xiàn)方式:使用組函數(shù)的特殊用法:
--簡單方式 :使用sum()
--函數(shù)的特殊用法:在方法里面威沫,添加 case語句
select
empid,
SUM(case when custid=1 then qty end) as A,--這樣 將已經(jīng)對empid 進行了限制
SUM(case when custid=2 then qty end) as B,
SUM(case when custid=3 then qty end) as C,
SUM(qty) as sumqty
from Orders9 group by empid
第三種方式:使用pivot贤惯,是 sql server 特有的,在oracle里面沒有:
select
empid,[1],[2],[3] 3 from
(
--僅僅查詢出 在 透視 里面需要用到的數(shù)據(jù)
select
empid,custid,qty
from Orders
) as t --在這里已經(jīng)對數(shù)據(jù) 進行了分組
pivot
(
sum(qty) --聚合函數(shù) (對那個列 執(zhí)行 組函數(shù))
for custid in ([1],[2],[3]) -- (對那些數(shù)據(jù)進行了聚合運算)
--這里的數(shù)字一定要 加[]
) as p
這種 使用 sql server 里面內(nèi)置的 pivot 的方法棒掠,肯定是比上面兩種自己寫的方法的效率高孵构。