也谈《每个分类取最新的几条的SQL实现》
其实一直没有碰到过这样的问题，前天看了《每个分类取最新的几条的SQL实现》这个帖子以后就一直在琢磨如果在没有row_Number的SqlServer2000下会不会很麻烦？（因为最近一年一直在用SQLServer2000）
所以昨天天在公司没事做的时候试着写了几个。
建表：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ClassTable]
GO
CREATE TABLE [dbo].[ClassTable] (
[id] [varchar] (10) COLLATE Japanese_CI_AS NOT NULL ,
[class] [varchar] (15) COLLATE Japanese_CI_AS NOT NULL ,
[name] [varchar] (15) COLLATE Japanese_CI_AS NULL ,
[testdate] [smalldatetime] NULL ,
CONSTRAINT [PK_ClassTable] PRIMARY KEY CLUSTERED
(
[id],
[class]
) ON [PRIMARY]
) ON [PRIMARY]
GO
测试数据：
delete ClassTable
declare @row int
declare @gRow int
set @gRow=1
WHILE (@gRow<=500)
BEGIN
declare @gStr varchar(20)
set @gStr=cast(@gRow as varchar)
set @row=1
WHILE (@row<=100)
BEGIN
declare @rowstr varchar(20)
set @rowstr=cast(@row as varchar)
print @rowstr
insert into ClassTable (id,class,name,testdate)
select @rowstr,'GROUP'+@gStr,'GROUP'+@gStr+' ' + @rowstr,Convert(Char(10),DATEADD(day,@row, getdate()),111)
set @row=@row+1
END
set @gRow=@gRow+1
END
插入50000条数据，分成500组
第一次测试。。。时间不太理想
SELECT id,class,name,testdate FROM ClassTable as ct1
WHERE ct1.testdate in (SELECT top 5 testDate FROM ClassTable as ct2 WHERE ct2.class=ct1.class GROUP BY testdate)
ORDER BY class, testdate
用EXISTS替代in。。。时间还是不理想
SELECT id,class,name,testdate FROM ClassTable as ct1
WHERE EXISTS (
SELECT testDate FROM
(SELECT top 5 testdate FROM ClassTable as ct2 WHERE ct2.class=ct1.class GROUP BY testdate) as ct3
WHERE ct3.testdate=ct1.testdate
)
ORDER BY class, testdate
用ANY替代in。。。还是不理想
SELECT id,class,name,testdate FROM ClassTable as ct1
WHERE ct1.testdate =ANY (SELECT top 5 testDate FROM ClassTable as ct2 WHERE ct2.class=ct1.class GROUP BY testdate)
ORDER BY class, testdate
以上三种情况均用时9秒。（回头一看发现自己绕了一个弯，写出来的是相同的三个SQL只不过写法不同，执行效率，没有任何变化，后面粘贴IO记录，一看便知。）
再来一次，彻底摒弃in的思路，改用>，当然这种情况不能保证只查出5条记录，因为testDate有可能重复（不过如果ID列IDENTITY的情况可以考虑用ID比较）
SELECT id,class,name,testdate FROM ClassTable as ct1
WHERE testdate<= (
SELECT max(testDate) as testDate FROM
(SELECT top 5 testdate FROM ClassTable as ct2 WHERE ct2.class=ct1.class GROUP BY testdate) as ct3
)
ORDER BY class, testdate
运行时间1秒，以上测试均查出2500条数据。
还是不够理想不过后来开始干活了，下次有时间继续优化，不过如果是海量数据库觉得还是应该建立合理的索引更好一些。
4条语句IO记录比较
**************************************第一条*********************************
(2500 件処理されました)
テーブル 'ClassTable'。 スキャン回数 3、論理読み取り数 974、物理読み取り数 0、先行読み取り数 0。
テーブル 'Worktable'。 スキャン回数 50000、論理読み取り数 230485、物理読み取り数 0、先行読み取り数 0。
SQL Server 実行時間 :
CPU 時間 = 17625 ミリ秒、経過時間 = 9731 ミリ秒。
**************************************第二条*********************************
(2500 件処理されました)
テーブル 'ClassTable'。 スキャン回数 3、論理読み取り数 974、物理読み取り数 0、先行読み取り数 0。
テーブル 'Worktable'。 スキャン回数 50000、論理読み取り数 230485、物理読み取り数 0、先行読み取り数 0。
SQL Server 実行時間 :
CPU 時間 = 17625 ミリ秒、経過時間 = 9752 ミリ秒。
**************************************第三条*********************************
(2500 件処理されました)
テーブル 'ClassTable'。 スキャン回数 3、論理読み取り数 974、物理読み取り数 0、先行読み取り数 0。
テーブル 'Worktable'。 スキャン回数 50000、論理読み取り数 230485、物理読み取り数 0、先行読み取り数 0。
SQL Server 実行時間 :
CPU 時間 = 17187 ミリ秒、経過時間 = 9244 ミリ秒。
**************************************第四条*********************************
(2500 件処理されました)
テーブル 'ClassTable'。 スキャン回数 3、論理読み取り数 974、物理読み取り数 0、先行読み取り数 0。
テーブル 'Worktable'。 スキャン回数 500、論理読み取り数 102379、物理読み取り数 0、先行読み取り数 0。
SQL Server 実行時間 :
CPU 時間 = 1297 ミリ秒、経過時間 = 1164 ミリ秒。
-