欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

date=業(yè)務(wù)日期；Item=商品名稱；saleqty=銷售數(shù)量；

-- 建立測(cè)試數(shù)據(jù)（表）
create table test (Date varchar(10), item char(10),saleqty int)
insert test values(''2010-01-01'',''AAA'',8)
insert test values(''2010-01-02'',''AAA'',4)
insert test values(''2010-01-03'',''AAA'',5)
insert test values(''2010-01-01'',''BBB'',1)
insert test values(''2010-01-02'',''CCC'',2)
insert test values(''2010-01-03'',''DDD'',6)

需要實(shí)現(xiàn)的報(bào)表樣式：每一行既每一天，顯示所有商品（列）該天的銷售數(shù)量；

實(shí)現(xiàn)的方法和思路如下：

-- 實(shí)現(xiàn)結(jié)果的靜態(tài)SQL語句寫法
-- 整理報(bào)表需要的格式
select date,
case item when ''AAA'' then saleqty when null then 0 end as AAA,
case item when ''BBB'' then saleqty when null then 0 end as BBB,
case item when ''CCC'' then saleqty when null then 0 end as CCC,
case item when ''DDD'' then saleqty when null then 0 end as DDD
from test

-- 按日期匯總行
select date,
sum(case item when ''AAA'' then saleqty when null then 0 end) as AAA,
sum(case item when ''BBB'' then saleqty when null then 0 end) as BBB,
sum(case item when ''CCC'' then saleqty when null then 0 end) as CCC,
sum(case item when ''DDD'' then saleqty when null then 0 end) as DDD
from test 
group by date

-- 處理數(shù)據(jù)：將空值的欄位填入數(shù)字0；
select date,
isnull (sum(case item when ''AAA'' then saleqty end),0) as AAA,
isnull (sum(case item when ''BBB'' then saleqty end),0) as BBB,
isnull (sum(case item when ''CCC'' then saleqty end),0) as CCC,
isnull (sum(case item when ''DDD'' then saleqty end),0) as DDD
from test 
group by date

靜態(tài)SQL語句編寫完成！

-- 需要?jiǎng)討B(tài)實(shí)現(xiàn)的SQL部分
isnull (sum(case item when ''AAA'' then saleqty end),0) as AAA,
isnull (sum(case item when ''BBB'' then saleqty end),0) as BBB,
isnull (sum(case item when ''CCC'' then saleqty end),0) as CCC,
isnull (sum(case item when ''DDD'' then saleqty end),0) as DDD

-- 動(dòng)態(tài)語句的實(shí)現(xiàn)
select ''isnull (sum(case item when ''''''+item+'''''' then saleqty end),0) as [''+item+'']'' 
from (select distinct item from test) as a
-- 這一步很關(guān)鍵：利用結(jié)果集給變量賦值；

-- 完成！
declare @sql varchar(8000)
set @sql = ''select Date''
select @sql = @sql + '',isnull (sum(case item when ''''''+item+'''''' then saleqty end),0) as [''+item+'']'' 
from (select distinct item from test) as a
select @sql = @sql+'' from test group by date''
exec(@sql)

-- 刪除測(cè)試數(shù)據(jù)（表）
drop table test