求句sql，谢谢
item date pric num
A00012011-01-041000001
A00012011-05-081000002
A00012011-09-081000001
A00012011-12-081000002
A00022011-06-16 1000001
A00022011-12-08 1000001
B00012011-03-01 2000011
B00032011-07-01 200001
C00012011-06-01 30001058
C00022011-01-0130002007
D00012011-06-01 300101651
D00022011-02-01 0511
D00032011-08-01 3002210
D00012011-04-22 30030
H00012011-03-21 200001
表结构如上所示，
现在我要取成下面这样
item curr-num curr-pric 1-3月 1-3月(price) 3-6月 3-6月（price） 6-9月 6-9月（price） 9-12月 9-12（price）
item 唯一
curr-num 目前总量 curr-pric=目前总量*单价
1-3月 = 1到3月进货的量
3-6月 = 3到6月进货的量
依次类推（根据date字段判断）
1-3月（price）= 1-3月的量 * 单价
依次类推
求sql语句 非常感谢
[解决办法]
select item,sum(num) curr-num, sum(case when month(date) in (1,2,3) then num else 0 end) [1-3num], sum(case when month(date) in (4,5,6) then num else 0 end) [5-6num], ...from tbwhere ...group by item
[解决办法]
--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([item] varchar(5),[date] datetime,[pric] int,[num] int)goinsert [tbl]select 'A0001','2011-01-04',100000,1 union allselect 'A0001','2011-05-08',100000,2 union allselect 'A0001','2011-09-08',100000,1 union allselect 'A0001','2011-12-08',100000,2 union allselect 'A0002','2011-06-16',100000,1 union allselect 'A0002','2011-12-08',100000,1 union allselect 'B0001','2011-03-01',20000,11 union allselect 'B0003','2011-07-01',20000,1 union allselect 'C0001','2011-06-01',3000,1058 union allselect 'C0002','2011-01-01',3000,2007 union allselect 'D0001','2011-06-01',300,101651 union allselect 'D0002','2011-02-01',0,511 union allselect 'D0003','2011-08-01',300,2210 union allselect 'D0001','2011-04-22',300,30 union allselect 'H0001','2011-03-21',20000,1/*表结构如上所示，现在我要取成下面这样item curr-num curr-pric 1-3月 1-3月(price) 3-6月 3-6月（price） 6-9月 6-9月（price）9-12月 9-12（price）item 唯一 curr-num 目前总量 curr-pric=目前总量*单价1-3月 = 1到3月进货的量3-6月 = 3到6月进货的量依次类推（根据date字段判断）1-3月（price）= 1-3月的量 * 单价依次类推求sql语句 非常感谢*/select [item],sum(num) as [curr-num],[pric] as [curr-pric], sum(case when month([date]) in (1,2,3) then num*[pric] else 0 end) [1-3月], sum(case when month([date]) in (4,5,6) then num*[pric] else 0 end) [5-6月], sum(case when month([date]) in (7,8,9) then num*[pric] else 0 end) [7-9月], sum(case when month([date]) in (10,11,12) then num*[pric] else 0 end) [9-12月]from tbl group by [item],[pric] order by [item]/*item curr-num curr-pric 1-3月 5-6月 7-9月 9-12月A0001 6 100000 100000 200000 100000 200000A0002 2 100000 0 100000 0 100000B0001 11 20000 220000 0 0 0B0003 1 20000 0 0 20000 0C0001 1058 3000 0 3174000 0 0C0002 2007 3000 6021000 0 0 0D0001 101681 300 0 30504300 0 0D0002 511 0 0 0 0 0D0003 2210 300 0 0 663000 0H0001 1 20000 20000 0 0 0*/
[解决办法]
create table #t([item] varchar(5),[date] datetime,[pric] int,[num] int,月份范围 varchar(10))insert #tselect *,case when month([date]) in (1,2,3) then '1-3月份' when month([date]) in (4,5,6) then '4-6月份' when month([date]) in (7,8,9) then '7-9月份' else '10-12月份' end as 月份范围from tbldeclare @str varchar(2000)set @str=''select @str=@str+','+'['+月份范围+']'+'=sum(case when 月份范围='+QUOTENAME(月份范围,'''')+' then num*[pric] else 0 end)' from #t group by 月份范围print @strset @str='select [item] as 编号, sum(num) as 当前数量, [pric] as 当前价格'+@str+' from #t group by [item],[pric] order by [item],[pric]'exec(@str)/*编号 当前数量 当前价格 1-3月份 4-6月份 7-9月份 10-12月份A0001 6 100000 100000 200000 100000 200000A0002 2 100000 0 100000 0 100000B0001 11 20000 220000 0 0 0B0003 1 20000 0 0 20000 0C0001 1058 3000 0 3174000 0 0C0002 2007 3000 6021000 0 0 0D0001 101681 300 0 30504300 0 0D0002 511 0 0 0 0 0D0003 2210 300 0 0 663000 0H0001 1 20000 20000 0 0 0*/--动态生成
[解决办法]
select item , [curr-num] = sum(num), [curr-pric] = sum(pric * num), [1_3_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end) [1_3_num], [1_3_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end) [1_3_price], [4_6_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end) [4_6_num], [4_6_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end) [4_6_price], [7_9_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end) [7_9_num], [7_9_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end) [7_9_price], [10_12_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end) [10_12_num], [10_12_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end) [10_12_price]from tbgroup by item
[解决办法]
create table [tb]([item] varchar(5),[date] datetime,[pric] int,[num] int)goinsert [tb]select 'A0001','2011-01-04',100000,1 union allselect 'A0001','2011-05-08',100000,2 union allselect 'A0001','2011-09-08',100000,1 union allselect 'A0001','2011-12-08',100000,2 union allselect 'A0002','2011-06-16',100000,1 union allselect 'A0002','2011-12-08',100000,1 union allselect 'B0001','2011-03-01',20000,11 union allselect 'B0003','2011-07-01',20000,1 union allselect 'C0001','2011-06-01',3000,1058 union allselect 'C0002','2011-01-01',3000,2007 union allselect 'D0001','2011-06-01',300,101651 union allselect 'D0002','2011-02-01',0,511 union allselect 'D0003','2011-08-01',300,2210 union allselect 'D0001','2011-04-22',300,30 union allselect 'H0001','2011-03-21',20000,1goselect item , [curr-num] = sum(num), [curr-pric] = sum(pric * num), [1_3_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end), [1_3_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end), [4_6_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end), [4_6_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end), [7_9_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end), [7_9_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end), [10_12_num] = sum(case when datepart(mm,date) between 1 and 3 then num else 0 end), [10_12_price] = sum(case when datepart(mm,date) between 1 and 3 then pric * num else 0 end)from tbgroup by itemdrop table tb/*item curr-num curr-pric 1_3_num 1_3_price 4_6_num 4_6_price 7_9_num 7_9_price 10_12_num 10_12_price ----- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- A0001 6 600000 1 100000 1 100000 1 100000 1 100000A0002 2 200000 0 0 0 0 0 0 0 0B0001 11 220000 11 220000 11 220000 11 220000 11 220000B0003 1 20000 0 0 0 0 0 0 0 0C0001 1058 3174000 0 0 0 0 0 0 0 0C0002 2007 6021000 2007 6021000 2007 6021000 2007 6021000 2007 6021000D0001 101681 30504300 0 0 0 0 0 0 0 0D0002 511 0 511 0 511 0 511 0 511 0D0003 2210 663000 0 0 0 0 0 0 0 0H0001 1 20000 1 20000 1 20000 1 20000 1 20000（所影响的行数为 10 行）*/