拓狼
-
个人空间
- 组别:管理员
- 性别:
- 来自:北京 海淀
- 积分:1121
- 帖子:619
- 注册:
2007-05-23
|
找到第N个最大或最小的记录的有效方法
找到第N个最大或最小的记录的有效方法--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址我们经常需要从记录集里找到第N个（第五或第八或第十）最大或最小的记录。在Oracle和MSSQL里的方法有所不同，因为在Oracle可以用RowNum。一段时间以前我在一个超过2亿条记录有56个varchar列和一个int列的大表上要实现同样的问题。我试了不同的解决方法（从网上找到的），但性能都非常差。于是我决定着手去找到这个问题更有效的解决方法。CREATE TABLE [Risk] (
[Col001] [varchar] (200) NULL ,
[Col002] [varchar] (200) NULL ,
[Col003] [varchar] (10) NULL,
[RowNum_Risk] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_risk] PRIMARY KEY CLUSTERED
(
[RowNum_Risk]
) ON [PRIMARY]
) ON [PRIMARY]
现在让我们向表里插入一些记录：INSERT INTO Risk Values('AAA1','BBB1','CCC1',1);
INSERT INTO Risk Values('AAA2','BBB2','CCC2',2);
INSERT INTO Risk Values('AAA3','BBB3','CCC3',3);
INSERT INTO Risk Values('AAA4','BBB4','CCC4',4);
INSERT INTO Risk Values('AAA5','BBB5','CCC5',5);
INSERT INTO Risk Values('AAA6','BBB6','CCC6',6);
INSERT INTO Risk Values('AAA7','BBB7','CCC7',7);
INSERT INTO Risk Values('AAA8','BBB8','CCC8',8);
INSERT INTO Risk Values('AAA9','BBB9','CCC9',9);
INSERT INTO Risk Values('AAA10','BBB10','CCC10',10);查询如下：SELECT Col001,Col002,Col003,RowNum_Risk FROM Risk
WHERE RowNum_Risk = (SELECT MIN(RowNum_Risk) FROM (SELECT DISTINCT TOP 5 RowNum_Risk FROM Risk ORDER BY RowNum_Risk DESC)B)查询将返回表的第五个最大的记录。如果你想找第八个或第十个或任意一个最大的记录，把5替换为你想要的数字即可。现在让我们看看执行计划。MSSQL为“SELECT DISTINCT TOP 5 RowNum_Risk FROM Risk ORDER BY RowNum_Risk DESC”使用索引扫描去得到第N条记录。然后为外部查询使用索引搜索去产生结果。结论：这是我发现从MSSQLServer的大表里找到第N个最大或最小记录的最好的方法。然而当你试图从数据集里找到比方说第1百万条最大的记录，那么查询计划时间将更长。但都在数分钟内能完成而不是在数小时之内。
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。
|
