请教按周统计产量的sql语句写法
例如：原始表字段是：产品ID，入库日期，入库数量
参数是开始日期、结束日期，日期是年月日
需要写成的报表如下：
产品ID，第一周数量，第二周数量，…… 合计
A 100 150…… ？
B 30 40 …… ？
…… …… …… ……
create table #temp
(
GoodsID nvarchar(10),
date datetime,
amount int
)
drop table #temp
insert into #temp select 'A','2010-08-02',200
insert into #temp select 'A','2010-08-10',300
insert into #temp select 'A','2010-08-11',300
insert into #temp select 'A','2010-08-17',400
insert into #temp select 'A','2010-08-23',500
insert into #temp select 'B','2010-08-02',200
insert into #temp select 'B','2010-08-10',300
insert into #temp select 'B','2010-08-17',400
insert into #temp select 'B','2010-08-23',500
declare @maxdate datetime,@mindate datetime
select @maxdate=max(date),@mindate=min(date) from #temp
;with temp2 as (
select GoodsID,amount,datediff(week,date,@maxdate)+1 as week from #temp
), temp3 as(
select GoodsID,week,sum(amount) as allcount from temp2 group by GoodsID,week
)
SELECT GoodsID,[1] as 第一周,[2] as 第二周,[3] as 第三周,[4] as 第四周
FROM temp3
PIVOT ( max(allcount) FOR week IN --行专列
([1],[2],[3],[4])
) AS week
ORDER BY GoodsID
declare @maxdate datetime,@weekcount int ,@sql nvarchar(max),@sql2 nvarchar(max),@sql3 nvarchar(max)
set @sql='' set @sql2='' set @sql3=''
select @maxdate=max(date) from #temp
select @weekcount=datediff(week,min(date),@maxdate)+1 from #temp
While (@weekcount>0)
begin
set @sql=@sql+'['+cast(@weekcount as char(1))+ '] as 第'+cast(@weekcount as char(1))+'周 ,'
set @sql2=@sql2+'['+cast(@weekcount as char(1))+'],'
set @weekcount=@weekcount-1
end
set @sql3='SELECT '+@sql+' GoodsID FROM (select
GoodsID,week,sum(amount) as allcount from (select
GoodsID,amount,datediff(week,date,(select top 1 max(date) from #temp )
)+1 as week from #temp) as A group by GoodsID,week ) as B PIVOT (
max(allcount) FOR week IN ('+left(@sql2,len(@sql2)-1)+') ) AS
week ORDER BY GoodsID ';
exec (@sql3)
