还是行转列的问题

wowo05 2010-10-11 11:55:12
一个日期表,日期是不固定的
sj(sj)
2010-08
2010-09
2010-10

一个奖金数表
id sj yf(应发) sf(实发)
001 2010 -10 100 0
001 2010 -09 100 -20
001 2010 -08 100 -100

002 2010 -10 200 -100
002 2010 -08 200 -100
002 2010 -07 200 -200

003 2010 -10 300 -100
003 2010 -09 300 -300
003 2010 -08 300 -300


想达到根据时间表中的时间,来转行奖金数
id 2010-08 2010-08 2010-09 2010-09 2010-10 2010-10 sum
001 100 -100 100 -20 100 0 180
002 200 -100 0 0 200 -100 200
003 300 -300 300 -300 300 -100 200


没有包含在日期表的不统计

...全文
64 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
chuifengde 2010-10-11
CREATE TABLE #x1(sj VARCHAR(20))
INSERT #x1 SELECT '2010-08'
union all select '2010-09'
union all select '2010-10'

CREATE TABLE #x2(id CHAR(3),sj VARCHAR(20), yf INT, sf INT)
INSERT #x2 SELECT '001', '2010-10', 100, 0
union all select '001', '2010-09', 100, -20
union all select '001', '2010-08', 100, -100
union all select '002', '2010-10', 200, -100
union all select '002', '2010-08', 200, -100
union all select '002', '2010-07', 200, -200
union all select '003', '2010-10', 300, -100
union all select '003', '2010-09', 300, -300
union all select '003', '2010-08', 300, -300


DECLARE @sql VARCHAR(8000)
SELECT @sql =ISNULL(@sql+',','')+' sum(case when a.sj='''+sj+''' then yf else 0 end) ['+sj+'(yf)],
sum(case when a.sj='''+sj+''' then sf else 0 end) ['+sj+'(sf)]'
FROM #x1
EXEC('select id,'+@sql+',sum(yf+sf) [sum] from #x1 a inner join #x2 b on a.sj=b.sj group by id')

DROP TABLE #x1,#x2
--result
/*id 2010-08(yf) 2010-08(sf) 2010-09(yf) 2010-09(sf) 2010-10(yf) 2010-10(sf) sum
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
001 100 -100 100 -20 100 0 180
002 200 -100 0 0 200 -100 200
003 300 -300 300 -300 300 -100 200

*/
回复
SQLCenter 2010-10-11
--> 测试数据:#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(sj varchar(8))
insert into #1
select '2010-08' union all
select '2010-09' union all
select '2010-10'
--> 测试数据:#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id varchar(8), sj varchar(8), yf int, sf int)
insert into #2
select '001', '2010-10', 100, 0 union all
select '001', '2010-09', 100, -20 union all
select '001', '2010-08', 100, -100 union all
select '002', '2010-10', 200, -100 union all
select '002', '2010-08', 200, -100 union all
select '002', '2010-07', 200, -200 union all
select '003', '2010-10', 300, -100 union all
select '003', '2010-09', 300, -300 union all
select '003', '2010-08', 300, -300

declare @sql varchar(8000)
select @sql = isnull(@sql+',','')
+ '['+sj+'.yf]=sum(case when sj='''+sj+''' then yf else 0 end),'
+ '['+sj+'.sf]=sum(case when sj='''+sj+''' then sf else 0 end)'
from #1

exec ('select id,'+@sql+', [sum]=sum(yf+sf) from #2 where sj in (select sj from #1) group by id')

/*
id 2010-08.yf 2010-08.sf 2010-09.yf 2010-09.sf 2010-10.yf 2010-10.sf sum
-------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
001 100 -100 100 -20 100 0 180
002 200 -100 0 0 200 -100 200
003 300 -300 300 -300 300 -100 200
*/
回复
SQLCenter 2010-10-11
--> 测试数据:#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(sj varchar(8))
insert into #1
select '2010-08' union all
select '2010-09' union all
select '2010-10'
--> 测试数据:#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(id varchar(8), sj varchar(8), yf int, sf int)
insert into #2
select '001', '2010-10', 100, 0 union all
select '001', '2010-09', 100, -20 union all
select '001', '2010-08', 100, -100 union all
select '002', '2010-10', 200, -100 union all
select '002', '2010-08', 200, -100 union all
select '002', '2010-07', 200, -200 union all
select '003', '2010-10', 300, -100 union all
select '003', '2010-09', 300, -300 union all
select '003', '2010-08', 300, -300

declare @sql varchar(8000)
select @sql = isnull(@sql+',','')
+ '['+sj+'.yf]=sum(case when sj='''+sj+''' then yf else 0 end),'
+ '['+sj+'.sf]=sum(case when sj='''+sj+''' then sf else 0 end)'
from #1

exec ('select id,'+@sql+' from #2 group by id')

/*
id 2010-08.yf 2010-08.sf 2010-09.yf 2010-09.sf 2010-10.yf 2010-10.sf
-------- ----------- ----------- ----------- ----------- ----------- -----------
001 100 -100 100 -20 100 0
002 200 -100 0 0 200 -100
003 300 -300 300 -300 300 -100
*/
回复
wowo05 2010-10-11
根据前面2个表,得出一个统计表出来
回复
webchanger 2010-10-11
什么问题呢?没明白问题
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

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