本文介绍如何使用SQL进行订单数据的操作与分析，包括创建订单表、插入数据、分组查询以及利用聚合函数对不同客户群体的订单量进行汇总分析，最后通过SQL的PIVOT和UNPIVOT方法对数据进行透视展示。
if OBJECT_ID('Orders')is not null
drop table Orders
go
create table Orders
(
orderid int not null,
orderdate date not null,
empid int not null,
cusid varchar(5)not null,
qty int not null,
constraint PK_Order primary key(orderid)
)
insert into Orders
(
orderid,orderdate,empid,cusid,qty
)
select 30001,'20070802',3,'A',10
union all
select 10001,'20071224',2,'A',12
union all
select 10005,'20071224',1,'B',20
union all
select 40001,'20080109',2,'A',40
union all
select 10006,'20080118',1,'C',14
union all
select 20001,'20080212',2,'B',12
union all
select 40005,'20080212',3,'A',10
union all
select 20002,'20080216',1,'C',20
union all
select 20003,'20090418',2,'B',15
union all
select 30004,'20070418',3,'C',
22
union all
select 30007,'20090907',3,'D',30
go
--sql2000Staticsql
select empid,
SUM(case when cusid='A'then qty else 0 end)'A',
SUM(case when cusid='B'then qty else 0 end)'B',
SUM(case when cusid='C'then qty else 0 end)'C',
SUM(case when cusid='D'then qty else 0 end)'D'
from
Orders group by empid
--sql2000Dynamicsql
declare @sql varchar(2000)
set @sql='select empid'
select @sql=@sql+',sum(case when cusid='''+O.cusid+''' then qty else 0 end)['+O.cusid+']'
from(select distinct cusid from Orders)O
set @sql=@sql+'from Orders group by empid'
exec(@sql)
--PIVOTMethod
select empid,A,B,C,D
--intoCusOrders
from
(
select empid,cusid,qty from Orders
)
sourceOrder
pivot
(
sum(qty)
for cusid in(A,B,C,D)
)
as
targetOrder
--UNPIVOTMethod
select
empid,cusid,qty from CusOrders
unpivot
(
qty for cusid in(A,B,C,D)
)
as[Target]
--Groupingsets
select empid,cusid,SUM(qty) qty
from Orders
group by Grouping
sets
(
(empid,cusid),
(empid),
(cusid),
()
)
--cube
--CubeisequivalenttoGROUPINGSets
select empid,cusid,SUM(qty) qty
from Orders
group by cube(empid,cusid)
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
