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

Question

表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
             
请问各位高手,是否还有更快的写法？

Answer 1

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

Answer 2

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.3create 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 行）

Answer 3

你的思路是对的，但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

Answer 4

最后少了一个括号：
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测试通过。

Answer 5

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值

Answer 6

靠，写错一些，改正：仍然没有测试过 :)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

Answer 7

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

Answer 8

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 

Answer 9

这个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

Answer 10

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

Answer 11

你的得不出所要的结果呀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