怎么把取出来的数据放到临时表的不同字段中去
我想做一个存储过程,是要输入一个日期,取这个日期所在周以及之后5周的销售情况,对这个日期进行判断,然后循环,我怎么把这几次循环取出来的数据分别以第一周第二周第三周的字段名存到临时表中
[解决办法]
你这个循环是每次生成一临时表 #temp_table_1
应该是先建好临时表,再循环插入
create table #temp(cSOCode varchar(100),iQuantity int)
...............
while @i<7
begin
insert into #temp
select a.cSOCode,sum(b.iQuantity)
from SO_SOMain a
join SO_SODetails b on a.cSOCode = b.cSOCode
where dDate between @a and @b
group by a.cSOCode
set @a=dateadd(day,1,@b)
set @b=dateadd(day,7,@b)
set @i=@i+1
end
DECLARE @date DATETIME
SET @date = '2013-08-28'
;WITH num AS
(
SELECT id = 1 UNION ALL
SELECT id = 2 UNION ALL
SELECT id = 3 UNION ALL
SELECT id = 4 UNION ALL
SELECT id = 5 UNION ALL
SELECT id = 6
),
cte_week AS
(
select
id = '第'+LTRIM(b.id)+'周',
begindate = CASE b.id WHEN 1 THEN @date ELSE DATEADD(DAY, -(DATEPART(WEEKDAY, @date)-2), DATEADD(week, b.id-1, a.begindate)) END,
enddate = DATEADD(DAY, -(DATEPART(WEEKDAY, @date)-1)+1, DATEADD(week, b.id, a.begindate))
FROM (SELECT begindate = @date) a
CROSS JOIN num b
)
--SELECT * FROM cte_week
SELECT * FROM
(
SELECT
a.cSOCode ,
c.id,
totalQuantity = SUM(b.iQuantity)
FROM SO_SOMain a
INNER JOIN SO_SODetails b
ON a.cSOCode = b.cSOCode
INNER JOIN cte_week c
ON dDate >= c.begindate AND dDate < c.enddate
GROUP BY a.cSOCode, c.id
) m
PIVOT
(
MAX(totalQuantity) FOR id IN([第1周],[第2周],[第3周],[第4周],[第5周],[第6周])
) n