关于行列转换的问题

zdc198771 2010-09-16 03:52:02
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

怎么查出这样的结果啊.....来个大大教教
...全文
59 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zdc198771 2010-09-16
谢谢楼上的各位啦,很受用,我还是要研究研究
回复
喜-喜 2010-09-16
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 行受影响)
*/

回复
东那个升 2010-09-16
--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 行受影响)
*/
回复
zdc198771 2010-09-16
可是结果2的第4列是每天库存啊,今天+昨天的,我只能固定加上一个值.
这个怎么办.....帮帮忙吧....
回复
ws_hgo 2010-09-16
[Quote=引用 2 楼 zdc198771 的回复:]
恩,谢谢楼上的.后面3个也教教我吧?是一样的方法么?
[/Quote]
是的
照着写就行啦
主要是
case when then else end的用法
回复
zdc198771 2010-09-16
恩,谢谢楼上的.后面3个也教教我吧?是一样的方法么?
回复
ws_hgo 2010-09-16
我只列了部分数据

其余的找着这个写

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 行受影响)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-09-16 03:52
社区公告
暂无公告