数据库：sql2005
现有数据：
物品名称，数量，日期
A 350 2009-12-30
B 300 2009-12-30
C 390 2009-12-30
A 340 2009-12-29
B 320 2009-12-29
C 360 2009-12-29
A 370 2009-12-28
B 330 2009-12-28
C 320 2009-12-28
我要生成这样一个查询结果
第一列显示物品，第二列现在现在的数量，第三列显示与前一天的差，第四列显示与第前2天的差，并按与前一天的差排序
A 350 10 -20
B 300 -20 -30
C 390 -30 -70
[code="sql"]select a1.名称,a1.数量,
a1.数量-(select 数量 from 表名 where (TO_DAYS(a1.日期)- TO_DAYS(日期))=1 and a1.名称=名称) as '前一天之差'
,
a1.数量-(select 数量 from 表名 where (TO_DAYS(a1.日期)- TO_DAYS(日期))=2 and a1.名称=名称) as '前两天之差'
from 表名 a1
where 日期='2009-12-31'[/code]
[code="sql"]
select name '名称',(t.total-(select total from goods g where g.name=t.name and g.date=昨天日期) '昨天',
(t.total-(select total from goods g where g.name=t.name and g.date=前天日期) '前天' from goods t group by '昨天';
[/code]
[code="sql"]
select name '名称',(t.total-(select total from goods g where g.name=t.name and g.date=昨天) '昨天',
(t.total-(select total from goods g where g.name=t.name and g.date=前天) '前天' from goods t where t.date=今天 group by '昨天';
[/code]
呵呵，主要思路还是使用子查询哈，呵呵。
但是我还是有问题哈，你这样要是他前一天或者前两天没有录入呢？
[code="java"]select t1.name '物件' ,t1.count '现在数量' ,t2.nc '一天差数量',t3.nc '二天差数量' from
(
select name,count from product where ntime =
(select max(ntime) from product)
) as t1 ,
(
select p1.name ,(p1.count-p2.count) nc from product as p1 cross join product as p2 on p1.name =p2.name
and p1.ntime=date_add(p2.ntime,interval 1 day) and p1.ntime=
(select max(p.ntime) from product as p )
)as t2 ,
(
select p1.name ,(p1.count-p2.count) nc from product as p1 cross join product as p2 on p1.name =p2.name
and p1.ntime=date_add(p2.ntime,interval 2 day) and p1.ntime=
(select max(p.ntime) from product as p )
)as t3
where t1.name =t2.name and t3.name=t1.name and t3.name=t2.name;[/code]
要是前一天或前两天没有记录就用decode把空记录记为0就可以了嘛