工资查询SQL

工资查询SQL求助
xhgsslff1ff1jeff2ff2jeid年度 月份
张1100300AA1000BB500120121
张2200400AA1000BB500220121
张320050000320121
张4200600AA10000420121
张5200700AA10000520121
张6200800AA10000620121
张7200900AA1000BB100720121
张82001000AA10000820121
张92001100AA10000920121
张102001200AA100001020121
张11200130001001120121
张120014000CC1001220122
张220015000CC01320122
张32001600001420122
张420017000DD01520122
张520018000DD01620122


生成表１怎么实现
编号年度 月份ff
2012010120121AA
2012010220121BB
2012020120122CC
2012020220122DD

(编号列说明：年＋月＋编号）

生成表２怎么实现
xh1月份2月份3月份4月份5月份6月份7月份8月份9月份
13001600
26001700
37001800
48001900

  1月（gs+sl ）的和2月（gs+sl ）的和




[解决办法]

SQL code

--> 测试数据：[T1]if object_id('[T1]') is not null drop table [T1]create table [T1]([xh] varchar(4),[gs] int,[sl] int,[ff1] varchar(2),[ff1je] int,[ff2] varchar(2),[ff2je] int,[id] int,[年度] int,[月份] int)insert [T1]select '张1',100,300,'AA',1000,'BB',500,1,2012,1 union allselect '张2',200,400,'AA',1000,'BB',500,2,2012,1 union allselect '张3',200,500,null,0,null,0,3,2012,1 union allselect '张4',200,600,'AA',1000,null,0,4,2012,1 union allselect '张5',200,700,'AA',1000,null,0,5,2012,1 union allselect '张6',200,800,'AA',1000,null,0,6,2012,1 union allselect '张7',200,900,'AA',1000,'BB',100,7,2012,1 union allselect '张8',200,1000,'AA',1000,null,0,8,2012,1 union allselect '张9',200,1100,'AA',1000,null,0,9,2012,1 union allselect '张10',200,1200,'AA',1000,null,0,10,2012,1 union allselect '张11',200,1300,null,0,null,100,11,2012,1 union allselect '张1',200,1400,null,0,'CC',100,12,2012,2 union allselect '张2',200,1500,null,0,'CC',0,13,2012,2 union allselect '张3',200,1600,null,0,null,0,14,2012,2 union allselect '张4',200,1700,null,0,'DD',0,15,2012,2 union allselect '张5',200,1800,null,0,'DD',0,16,2012,2select * from [T1]--问题1:select LTRIM(年度)+right('00'+LTRIM(月份),2)+right('00'+LTRIM(ID),2) as 编号 ,年度,月份,ff from(select ROW_NUMBER()over(partition by 月份 order by ff) as ID,* from(select 年度,月份,ff1 as ff from [T1] unionselect 年度,月份,ff2 as ff from [T1])a where ff is not null)b--问题2:with tas(select [xh],ltrim([月份])+'月份' as 月份,sum([sl]+[gs]) as totalfrom [T1] where RIGHT(xh,LEN(XH)-1) in('1','2','3','4') group by [xh],[月份])select *from t pivot (sum([total]) for [月份] in([1月份],[2月份],[3月份],[4月份],[5月份],[6月份],[7月份],[8月份],[9月份]))b/*xh    1月份    2月份    3月份    4月份    5月份    6月份    7月份    8月份    9月份张1    400    1600    NULL    NULL    NULL    NULL    NULL    NULL    NULL张2    600    1700    NULL    NULL    NULL    NULL    NULL    NULL    NULL张3    700    1800    NULL    NULL    NULL    NULL    NULL    NULL    NULL张4    800    1900    NULL    NULL    NULL    NULL    NULL    NULL    NULL*/
我的异常网推荐解决方案：软件开发者薪资,http://www.myexception.cn/other/1391128.html