是高手的，进来！查询最近两条记录，感觉很难，各位高手，有什么方法最快？

[ntzhuhongbin]

表A:
id class date TestResult
1 2 2004-1-4 2.4
2 2 2003-11-4 2.2
3 1 2004-5-5 2.1
4 3 2004-4-4 2.5
5 1 2003-12-3 2.5
6 2 2004-3-5 2.3
.......

表B：
class name
1 aaa
2 bbb
3 ccc
....

现在我需要查询:

name 上次测定（最后的前一次） 最后测定 差异
1 2.5 2.1 -0.4
2 2.4 2.3 -0.1
3 null 2.5 null

我知道可以这样写：
select name,(select max(TestResult)
from a where date=(select max(TestResult)
from a where date < (select max(TestResult) from a))),
(select max(TestResult)
from a where date=(select max(TestResult) from a)),
(select max(TestResult)
from a where date=(select max(TestResult) from a)-
(select max(TestResult)
from a where date=(select max(TestResult)
from a where date < (select max(TestResult) from a))) from b

请问各位高手,是否还有更快的写法？

[qinzj27]

查询结果为
name 上次测定（最后的前一次） 最后测定 差异
1 2.5 2.1 -0.4
2 2.4 2.3 -0.1
3 null 2.5 null

看不明白

[jblcj]

太复杂了，看不懂

[lorvery]

看不懂，到底由两个表要得到什么结果？

[qizhanfeng]

create table A (id int , class int, date smalldatetime, TestResult numeric(18,4))
insert into A select 1, 2, '2004-1-4', 2.4
union select 2, 2, '2003-11-4', 2.2
union select 3, 1, '2004-5-5', 2.1
union select 4, 3, '2004-4-4', 2.5
union select 5, 1, '2003-12-3', 2.5
union select 6, 2, '2004-3-5', 2.3

create table B(class int, name varchar(20))
insert into B select 1, 'aaa'
union select 2, 'bbb'
union select 3, 'ccc'

--测试

select e.class,b.name, f.testresult as [上次测定(最后的前一次)], e.testresult as 最后测定,e.testresult-f.testresult as 差异 from
(select class,testresult from A where exists(
select * from (select class,max(date) as date from A group by class ) A1
where A.class=A1.class and A.date=A1.date
)) E left join
(
select class,max(testresult) testresult from
(
select class,testresult from A where not exists(select * from
(
select class,max(date) as date from A group by class
) as C where A.date= C.date and A.class=C.class)
) D
group by class) F on e.class=f.class inner join B on e.class=B.class
order by e.class

--结果
class name 上次测定(最后的前一次) 最后测定 差异
----------- -------------------- -------------------- -------------------- ---------------------
1 aaa 2.5000 2.1000 -.4000
2 bbb 2.4000 2.3000 -.1000
3 ccc NULL 2.5000 NULL

（所影响的行数为 3 行）

[dejiang]

你的思路是对的，但SQL应该是这样：
select name,(select max(TestResult)
from a where date=(select max(date)
from a where date < (select max(date) from a))),
(select max(TestResult)
from a where date=(select max(date) from a)),
(select max(TestResult)
from a where date=(select max(date) from a)-
(select max(TestResult)
from a where date=(select max(date)
from a where date < (select max(date) from a))) from b

[dejiang]

最后少了一个括号：
select name,(select max(TestResult)
from a where date=(select max(date)
from a where date < (select max(date) from a))),
(select max(TestResult)
from a where date=(select max(date) from a)),
(select max(TestResult)
from a where date=(select max(date) from a)-
(select max(TestResult)
from a where date=(select max(date)
from a where date < (select max(date) from a)))) from b

测试通过。

[ccton]

SELECT
b.class,
b.name,
result_current,
result_current - result_last as result_change
FROM
(
SELECT
a0.class_0 as the_class,
a0.TestResult as result_current,
a.TestResult as result_last
FROM
(
SELECT
min(class) as class_0,
max(date) as date_max,
TestResult
FROM a
GROUP BY class, date
}.. as a0
LEFT JOIN a ON a0.class_0=a.class
WHERE a.date>a0.date_max
GROUP BY a0.class_0
) as a1 LEFT JOIN b ON a1.the_class=b.class

俺随便说说。
两个子查询，第一个子查询（得到a0）求出按class,date 进行GROUP的max(date)和class；第二个子查询（得到a1）根据第一个子查询结果求出倒数第二次的date来。
注意可以把最后的主查询放在求a2的语句里一次性完成。我把它分成了三次查询，一是为了好理解，二是减少第2次查询的联结操作，换句话说按照现在的写法应该要比只有两次查询的执行速度快——如果只有两次查询，那么GROUP可能是在联结完表B并且把计算差值的减法都做完以后再进行的，无疑做了很多无用功。

要顺利执行这个SQL语句，需要在表A上建立一个多列索引(class,date)

俺很久没有碰MS_SQL了，所以语法不一定正确，大概是这个意思而已。

原来楼主给的式子很复杂，没仔细看，不过我没有看到 LEFT JOIN，都是逗号即相当于 INNER JOIN，怀疑楼主的语句能否得到class=3的NULL值

[ccton]

靠，写错一些，改正：仍然没有测试过 :)

SELECT
b.class,
b.name,
a00.TestResult as result_current,
a00.TestResult - a11.TestResult as result_change
FROM
(
SELECT
min(a0.class_0) as the_class,
min(a.date) as last_date,
min(date_max) as the_date_max

FROM
(
SELECT
min(class) as class_0,
max(date) as date_max,
FROM a
GROUP BY class, date
}.. as a0
LEFT JOIN a ON a0.class_0=a.class
WHERE a.date>a0.date_max
GROUP BY a0.class_0, a0.date_max, a.date
) as a1 LEFT JOIN b ON a1.the_class=b.class
LEFT JOIN a as a00 ON a1.the_class=a00.class AND a1.last_date=a00.date
LEFT JOIN a as a11 ON a1.the_class=a11.class AND a1.the_date_max=a11.date

[ccton]

为了避免忘了语法从而写错出问题，我已经把所有带GROUP的查询涉及的字段，全部加到group里并且使用统计函数 :)

[qizhanfeng]

select distinct class ,
(select testresult from (
select class,date,testresult,new_id=(select count(*) from A A1 where A1.class=A.class and A1.date>=A.date)
from A
) c where C.class=A.class and new_id=2 ) as [上次测定（最后的前一次)],
(select testresult from (
select class,date,testresult,new_id=(select count(*) from A A1 where A1.class=A.class and A1.date>=A.date)
from A
) c where C.class=A.class and new_id=1 ) as [最后测定],


(select testresult from (

select class,date,testresult,new_id=(select count(*) from A A1 where A1.class=A.class and A1.date>=A.date)
from A
) c where C.class=A.class and new_id=1 )-
(select testresult from (
select class,date,testresult,new_id=(select count(*) from A A1 where A1.class=A.class and A1.date>=A.date)
from A
) c where C.class=A.class and new_id=2 ) as [差异]
from A

[pdbird]

用exits，绝对比你现在的快。
你改一下吧。

[qizhanfeng]

你的得不出所要的结果呀

select name,上次测定（最后的前一次）=(select top 1 TestResult from 表A where class=a.class and date<a.date order by date desc), 最后测定=a.TestResult,
差异=a.TestResult-(select top 1 TestResult from 表A where class=a.class and date<a.date order by date desc)
from 表A a,表B b
where date=(select top 1 date from 表A where class=a.class order by date desc)
and a.class=b.class
order by name

[qizhanfeng]

在查询分析器里比较就知道了

[ntzhuhongbin]

这个sql是不是比你们写的快？

select name,上次测定（最后的前一次）=(select top 1 TestResult from 表A where class=a.class and date<a.date order by date desc), 最后测定=a.TestResult,
差异=a.TestResult-(select top 1 TestResult from 表A where class=a.class and date<a.date order by date desc)
from 表A a,表B b
where date=(select top 1 date from 表A where class=a.class order by date desc)
and a.class=b.class
order by name