表a
id 产品编号
checkDate 日期
Num 产品数量
id checkDate Num
1 2006-10-11 11
2 2006-10-11 22
1 2006-10-20 33
2 2006-10-20 44
1 2006-11-1 55
2 2006-11-1 66求2006-11-1和上一次日期(即2006-10-20)的产品数量
结果
id Num1 Num2
1 33 55
2 44 66
id 产品编号
checkDate 日期
Num 产品数量
id checkDate Num
1 2006-10-11 11
2 2006-10-11 22
1 2006-10-20 33
2 2006-10-20 44
1 2006-11-1 55
2 2006-11-1 66求2006-11-1和上一次日期(即2006-10-20)的产品数量
结果
id Num1 Num2
1 33 55
2 44 66
declare @t table(id int, checkDate datetime,Num int)
insert @t
select 1, '2006-10-11', 11 union all
select 2, '2006-10-11', 22 union all
select 1, '2006-10-20', 33 union all
select 2, '2006-10-20', 44 union all
select 1, '2006-11-1', 55 union all
select 2, '2006-11-1', 66----查询
select id,
Num1 = (select top 1 Num from @t where id = a.id and checkDate < a.checkDate order by checkDate desc),
Num2 = Num
from @t as a where checkDate = '2006-11-1'/*结果
id Num1 Num2
----------------------------------
1 33 55
2 44 66
*/
id int,
checkDate datetime,
Num int
)
insert tb select 1, '2006-10-11', '11'
union all select 2, '2006-10-11', '22'
union all select 1, '2006-10-20', '33'
union all select 2, '2006-10-20', '44'
union all select 1, '2006-11-1', '55'
union all select 2, '2006-11-1', '66'select id,
Num1=(select tmp.Num1 from(
select id, max(checkDate) as checkDate, max(A.Num) as Num1
from tb as A
where A.checkDate<>'2006-11-1'
group by A.id
) tmp where tmp.id=B.id
),
Num2=Num
from tb as B
where checkDate='2006-11-1'drop table tb
Num1=(
select Num from tb inner join(
select id, max(checkDate) as checkDate
from tb
where checkDate<>'2006-11-1'
group by id
) tmp on tmp.id=tb.id and tmp.checkDate=tb.checkDate and tmp.id=B.id
),
Num2=Num
from tb as B
where checkDate='2006-11-1'