日期:2014-05-18  浏览次数:20472 次

求句sql,谢谢
item date pric num
A0001 2011-01-04 100000 1
A0001 2011-05-08 100000 2
A0001 2011-09-08 100000 1
A0001 2011-12-08 100000 2
A0002 2011-06-16 100000 1
A0002 2011-12-08 100000 1
B0001 2011-03-01 20000 11
B0003 2011-07-01 20000 1
C0001 2011-06-01 3000 1058
C0002 2011-01-01 3000 2007
D0001 2011-06-01 300 101651
D0002 2011-02-01 0 511
D0003 2011-08-01 300 2210
D0001 2011-04-22 300 30
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语句 非常感谢

------解决方案--------------------
SQL code

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 tb
where ...
group by item

------解决方案--------------------
SQL code

--> 测试数据:[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[item] varchar(5),
[date] datetime,
[pric] int,
[num] int
)
go
insert [tbl]
select 'A0001','2011-01-04',100000,1 union all
select 'A0001','2011-05-08',100000,2 union all
select 'A0001','2011-09-08',100000,1 union all
select 'A0001','2011-12-08',100000,2 union all
select 'A0002','2011-06-16',100000,1 union all
select 'A0002','2011-12-08',100000,1 union all
select 'B0001','2011-03-01',20000,11 union all
select 'B0003','2011-07-01',20000,1 union all
select 'C0001','2011-06-01',3000,1058 union all
select 'C0002','2011-01-01',3000,2007 union all
select 'D0001','2011-06-01',300,101651 union all
select 'D0002','2011-02-01',0,511 union all
select 'D0003','2011-08-01',300,2210 union all
select 'D0001','2011-04-22',300,30 union all
select '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    200000
A0002    2    100000    0    100000    0    100000
B0001    11    20000    220000    0    0    0
B0003    1    20000    0    0    20000    0
C0001    1058    3000    0    3174000    0    0
C0002    2007    3000    6021000    0    0    0
D0001    101681    300    0    30504300    0    0
D0002    511    0    0    0    0    0
D0003    2210    300    0    0    663000    0
H0001    1    20000    20000    0    0    0
*/

------解决方案--------------------
SQL code

create table #t(
[item] varchar(5),
[date] datetime,
[pric] int,
[num] int,
月份范围 varchar(10)
)
insert #t
select *,
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 tbl


declare @str varchar(2000)
set @str=''
select @str=@str+','+'['+月份范围+']'+'=sum(case when 月份范围='+QUOTENAME(月份范围,'''')+
' then num*[pric] else 0 end)' from #t group by 月份范围
print @str
set @str='select [item] as 编号, sum(num) as