表结构如下:
FItemNo(料号) FDate(进货日期) FPrice(价格)
001 06-01-01 200
001 06-01-04 210
001 06-01-03 200
002 06-01-25 700
002 06-01-01 720
.....................................................
怎样写sql实现如下结果(统计最近4次进货时间及进价):
FItemNO FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3 FDate4 FPrice4
001 06-01-04 210 06-01-03 200 06-01-03 200 无 无
002 06-01-25 720 06-01-25 700 无 无 无 无
FItemNo(料号) FDate(进货日期) FPrice(价格)
001 06-01-01 200
001 06-01-04 210
001 06-01-03 200
002 06-01-25 700
002 06-01-01 720
.....................................................
怎样写sql实现如下结果(统计最近4次进货时间及进价):
FItemNO FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3 FDate4 FPrice4
001 06-01-04 210 06-01-03 200 06-01-03 200 无 无
002 06-01-25 720 06-01-25 700 无 无 无 无
insert into #t select '001','06-01-01',200
insert into #t select '001','06-01-04',210
insert into #t select '001','06-01-03',200
insert into #t select '002','06-01-25',700
insert into #t select '002','06-01-01',720declare @s varchar(8000),@i int
select @s='',@i=max(cnt) from (select count(distinct FDate) cnt from #t group by FItemNo) a
while @i>0
begin
set @s=',FDate'+ rtrim(@i)+'=max(case i when '+rtrim(@i)+' then FDate end)'+
',FPrice'+rtrim(@i)+'=sum(case i when '+rtrim(@i)+' then FPrice end)'+@s
set @i=@i-1
end
set @s='select FItemNo'+@s+' from (select t.*,(select count(distinct FDate) from #t where FItemNo=t.FItemNo and FDate<=t.FDate) i from #t t) a group by FItemNo'
exec(@s)/*
FItemNo FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3
------- ---------- ----------- ---------- ----------- ---------- -----------
001 06-01-01 200 06-01-03 200 06-01-04 210
002 06-01-01 720 06-01-25 700 NULL NULL
*/drop table #t
insert into #t select '001','06-01-01',200
insert into #t select '001','06-01-04',210
insert into #t select '001','06-01-03',200
insert into #t select '002','06-01-25',700
insert into #t select '002','06-01-01',720declare @s varchar(8000),@i int
select @s='',@i=4
while @i>0
begin
set @s=',FDate'+ rtrim(@i)+'=max(case i when '+rtrim(@i)+' then FDate end)'+
',FPrice'+rtrim(@i)+'=sum(case i when '+rtrim(@i)+' then FPrice end)'+@s
set @i=@i-1
end
set @s='select FItemNo'+@s+' from (select t.*,(select count(distinct FDate) from #t where FItemNo=t.FItemNo and FDate<=t.FDate) i from #t t) a group by FItemNo'
exec(@s)/*
FItemNo FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3 FDate4 FPrice4
------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
001 06-01-01 200 06-01-03 200 06-01-04 210 NULL NULL
002 06-01-01 720 06-01-25 700 NULL NULL NULL NULL
*/drop table #t
---------------------------------------------------------------------------------------------------------
declare @t table(FItemNo varchar(6),FDate varchar(10),FPrice int)
insert into @t select '001','06-01-01',200
insert into @t select '001','06-01-04',210
insert into @t select '001','06-01-03',200
insert into @t select '002','06-01-25',700
insert into @t select '002','06-01-01',720select
FItemNo,
FDate1=max(case i when 1 then FDate end),
FPrice1=sum(case i when 1 then FPrice end),
FDate2=max(case i when 2 then FDate end),
FPrice2=sum(case i when 2 then FPrice end),
FDate3=max(case i when 3 then FDate end),
FPrice3=sum(case i when 3 then FPrice end),
FDate4=max(case i when 4 then FDate end),
FPrice4=sum(case i when 4 then FPrice end)
from
(select t.*,(select count(distinct FDate) from @t where FItemNo=t.FItemNo and FDate<=t.FDate) i from @t t) a
group by
FItemNo/*
FItemNo FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3
------- ---------- ----------- ---------- ----------- ---------- -----------
001 06-01-01 200 06-01-03 200 06-01-04 210
002 06-01-01 720 06-01-25 700 NULL NULL
*/
insert into @t select '001','06-01-01',200
insert into @t select '001','06-01-04',210
insert into @t select '001','06-01-03',200
insert into @t select '002','06-01-25',700
insert into @t select '002','06-01-01',720select
FItemNo,
FDate1=max(case i when 1 then FDate end),
FPrice1=sum(case i when 1 then FPrice end),
FDate2=max(case i when 2 then FDate end),
FPrice2=sum(case i when 2 then FPrice end),
FDate3=max(case i when 3 then FDate end),
FPrice3=sum(case i when 3 then FPrice end),
FDate4=max(case i when 4 then FDate end),
FPrice4=sum(case i when 4 then FPrice end)
from
(select t.*,(select count(distinct FDate) from @t where FItemNo=t.FItemNo and FDate<=t.FDate) i from @t t) a
group by
FItemNo/*
FItemNo FDate1 FPrice1 FDate2 FPrice2 FDate3 FPrice3 FDate4 FPrice4
------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
001 06-01-01 200 06-01-03 200 06-01-04 210 NULL NULL
002 06-01-01 720 06-01-25 700 NULL NULL NULL NULL
*/
libin_ftsafe(子陌红尘) 老大真是了不起!