关于行列转换的问题

Question

SQL数据转换的问题
表如下
1 1123 存 2010-09-01
4 24521 取 2010-09-01
5 12345 取 2010-09-02
6 1232 存 2010-09-02
7 78525 存 2010-09-02
8 78721 取 2010-09-03
9 27752 存 2010-09-04
10 7273 存 2010-09-04
11 42272 存 2010-09-04
12 25722 取 2010-09-05
13 2751 存 2010-09-05
14 37253 取 2010-09-06
15 5436 存 2010-09-06
16 28727 存 2010-09-07
17 25777 取 2010-09-07
18 111 存 2010-09-07结果1
2010-09-01 1123 24521
2010-09-02 79757 12345
2010-09-03 0 78721
2010-09-04 77297 0
2010-09-05 2751 25722
2010-09-06 5436 37253
2010-09-07 28838 25777结果2
2010-09-01 1123 24521 -23398 -23398
2010-09-02 79757 12345 67412 44014
2010-09-03 0 78721 -78721 -34707
2010-09-04 77297 0 77297 42590
2010-09-05 2751 25722 -22971 19619
2010-09-06 5436 37253 -31817 -12198
2010-09-07 28838 25777 3061 -9137结果3
存 1123 79757 NULL 77297 2751 5436 28838
取 24521 12345 78721 NULL 25722 37253 25777结果4
存 1123 78525 NULL 42272 2751 5436 28727
取 24521 12345 78721 NULL 25722 37253 25777
库存 -23398 67412 -78721 77297 -22971 -31817 3061怎么查出这样的结果啊.....来个大大教教

Answer 1

我只列了部分数据其余的找着这个写if object_id('table1') is not null drop table table1
create table table1
(
  id int identity(1,1) primary key,
  Num int,
  status varchar(20),
  date datetime
)
insert into table1 select 1123,'存','2010-09-01'
union all select 24521,'取','2010-09-01'
union all select 12345,'取','2010-09-02'
union all select 1232,'存','2010-09-02'
union all select 78525,'存','2010-09-02'
union all select 78721,'取','2010-09-03'
union all select 27752,'存','2010-09-04'
select convert(varchar(10),date,120) 日期,
       sum(case when status='存' then Num else 0 end) '存',
       sum(case when status='取' then Num else 0 end) '取'
from table1
group by convert(varchar(10),date,120)日期         存           取
---------- ----------- -----------
2010-09-01 1123        24521
2010-09-02 79757       12345
2010-09-03 0           78721
2010-09-04 27752       0(4 行受影响)

Answer 2

是的
照着写就行啦
主要是
case when then else end的用法

Answer 3

--create table #test(id int,num int,type varchar(10),date varchar(10))
--insert #test select 1 ,1123 ,'存','2010-09-01'
--insert #test select 4 ,24521 ,'取','2010-09-01'
--insert #test select 5 ,12345 ,'取','2010-09-02'
--insert #test select 6 ,1232 ,'存','2010-09-02'
--insert #test select 7 ,78525 ,'存','2010-09-02'
--insert #test select 8 ,78721 ,'取','2010-09-03'
--insert #test select 9 ,27752 ,'存','2010-09-04'
--insert #test select 10, 7273 ,'存','2010-09-04'
--insert #test select 11 ,42272 ,'存','2010-09-04'
--insert #test select 12 ,25722 ,'取','2010-09-05'
--insert #test select 13 ,2751 ,'存','2010-09-05'
--insert #test select 14 ,37253 ,'取','2010-09-06'
--insert #test select 15 ,5436 ,'存','2010-09-06'
--insert #test select 16 ,28727 ,'存','2010-09-07'
--insert #test select 17 ,25777 ,'取','2010-09-07'
--insert #test select 18 ,111 ,'存','2010-09-07'
select date,
       sum(case when type='存' then Num else 0 end) '存',
       sum(case when type='取' then Num else 0 end) '取'
from #test
group by date;with cte as(
select date,
       sum(case when type='存' then Num else 0 end) '存',
       sum(case when type='取' then Num else 0 end) '取',
       sum(case when type='存' then Num else 0 end) -sum(case when type='取' then Num else 0 end)  as YE
from #test
group by date)
select *,isnull(YE+(select SUM(ISNULL(YE,0)) from cte where a.date>date ),YE) from cte  a
declare @col varchar(100)
set  @col=replace((select quotename(convert(varchar(10),date,120))  as 'data()'from  #test group by date
for xml path('')),' ',',')
exec('
select type,'+@col+' from (SELECT num,type,date from #test) a
pivot( sum(num) for date in('+@col+')) as pvt')exec('
select type,'+@col+' 
from (SELECT sum(case when type=''存'' then Num else 0 end) -sum(case when type=''取'' then Num else 0 end) as num
,''库存'' as type,date from #test
group by date
union all
SELECT num,type,date from #test) a
pivot( sum(num) for date in('+@col+')) as pvt')
/*
date       存           取
---------- ----------- -----------
2010-09-01 1123        24521
2010-09-02 79757       12345
2010-09-03 0           78721
2010-09-04 77297       0
2010-09-05 2751        25722
2010-09-06 5436        37253
2010-09-07 28838       25777(7 行受影响)date       存           取           YE          
---------- ----------- ----------- ----------- -----------
2010-09-01 1123        24521       -23398      -23398
2010-09-02 79757       12345       67412       44014
2010-09-03 0           78721       -78721      -34707
2010-09-04 77297       0           77297       42590
2010-09-05 2751        25722       -22971      19619
2010-09-06 5436        37253       -31817      -12198
2010-09-07 28838       25777       3061        -9137(7 行受影响)type       2010-09-01  2010-09-02  2010-09-03  2010-09-04  2010-09-05  2010-09-06  2010-09-07
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
存          1123        79757       NULL        77297       2751        5436        28838
取          24521       12345       78721       NULL        25722       37253       25777(2 行受影响)type       2010-09-01  2010-09-02  2010-09-03  2010-09-04  2010-09-05  2010-09-06  2010-09-07
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
存          1123        79757       NULL        77297       2751        5436        28838
库存         -23398      67412       -78721      77297       -22971      -31817      3061
取          24521       12345       78721       NULL        25722       37253       25777(3 行受影响)
*/

Answer 4

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
ID int,
金额 int,
操作 char(3),
日期 datetime
)
go
--插入测试数据
insert into tb select 1,1123,'存','2010-09-01'
union all select 4,24521,'取','2010-09-01'
union all select 5,12345,'取','2010-09-02'
union all select 6,1232,'存','2010-09-02'
union all select 7,78525,'存','2010-09-02'
union all select 8,78721,'取','2010-09-03'
union all select 9,27752,'存','2010-09-04'
union all select 10,7273,'存','2010-09-04'
union all select 11,42272,'存','2010-09-04'
union all select 12,25722,'取','2010-09-05'
union all select 13,2751,'存','2010-09-05'
union all select 14,37253,'取','2010-09-06'
union all select 15,5436,'存','2010-09-06'
union all select 16,28727,'存','2010-09-07'
union all select 17,25777,'取','2010-09-07'
union all select 18,111,'存','2010-09-07'
go
--代码实现select [日期]=convert(varchar(10),[日期],120)
,[存]=sum(case when [操作]='存' then [金额] else 0 end)
,[取]=sum(case when [操作]='取' then [金额] else 0 end)
from tb group by [日期]/*测试结果日期          存     取
-------------------------------
2010-09-01 1123 24521
2010-09-02 79757 12345
2010-09-03 0 78721
2010-09-04 77297 0
2010-09-05 2751 25722
2010-09-06 5436 37253
2010-09-07 28838 25777(7 行受影响)
*/;with t as(
select idd=row_number()over(order by [日期])
,[日期]=convert(varchar(10),[日期],120)
,[存]=sum(case when [操作]='存' then [金额] else 0 end)
,[取]=sum(case when [操作]='取' then [金额] else 0 end)
,[库存1]=sum(case when [操作]='存' then [金额] else 0 end)-sum(case when [操作]='取' then [金额] else 0 end)
from tb group by [日期]
)
,tt as(
select *,[库存2]=[库存1] from t where idd=1
union all
select t.*,[库存2]=t.[库存1]+tt.[库存2] from t,tt where t.idd=tt.idd+1
)
select * from tt/*测试结果
 
日期          存       取       库存1     库存2
-------------------------------------------------
2010-09-01 1123 24521 -23398 -23398
2010-09-02 79757 12345 67412 44014
2010-09-03 0 78721 -78721 -34707
2010-09-04 77297 0 77297 42590
2010-09-05 2751 25722 -22971 19619
2010-09-06 5436 37253 -31817 -12198
2010-09-07 28838 25777 3061 -9137(7 行受影响)
*/