在sql Server中,我总是发现获取数据集的最大行很痛苦,我正在寻找一些方法列表来检索最大行,并提供有关性能和可维护性的一些指导.
样本表：
DECLARE @Test TABLE (ID INT IDENTITY(1,1),name VARCHAR(50),dateOfBirth DATETIME,TaxNumber varchar(10)) INSERT INTO @Test (name,dateOfBirth,TaxNumber) SELECT 'Fred',convert(datetime,'25/01/1976',103),'123' UNION ALL SELECT 'Bob','03/03/1976','234' UNION ALL SELECT 'Jane','13/06/1996','345' UNION ALL SELECT 'Fred','14/02/1982','456' UNION ALL SELECT 'Bob','25/10/1983','567' UNION ALL SELECT 'Jane','12/04/1995','678' UNION ALL SELECT 'Fred','789' select * from @Test
得到：
ID name dateOfBirth TaxNumber ----------- --------- ----------------------- ---------- 1 Fred 1976-01-25 00:00:00.000 123 2 Bob 1976-03-03 00:00:00.000 234 3 Jane 1996-06-13 00:00:00.000 345 4 Fred 1982-02-14 00:00:00.000 456 5 Bob 1983-10-25 00:00:00.000 567 6 Jane 1995-04-12 00:00:00.000 678 7 Fred 1976-03-03 00:00:00.000 789
如果我想要检索最老的人(按名称分组)完整的详细信息,我可以使用哪些方法？
期望的输出：
ID name dateOfBirth TaxNumber ----------- --------- ----------------------- ---------- 1 Fred 1976-01-25 00:00:00.000 123 2 Bob 1976-03-03 00:00:00.000 234 6 Jane 1995-04-12 00:00:00.000 678
解决方法
两种常用方法：聚合和排名功能.
聚合适用于sql Server 2000.两种方式都可以使用CTE或派生表
为了性能,我发现聚合效果更好.但是,看起来sql Server 2008排名功能的运行远远好于sql Server 2005.我不是日常使用sql Server 2008(大型dinsoaur公司)所以无法发表评论.
有两个相关的SO问题,但我目前找不到它们.一个是关于具有排名功能的高逻辑IO的问题,另一个是关于sql 2k5与2k8的评论中的排名测试.抱歉.
--aggregate + CTE
;WITH cOldest AS
(
SELECT name,MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
)
SELECT
T.*
FROM
@Test T
JOIN
cOldest C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
T.ID
--aggregate + derived table
SELECT
T.*
FROM
@Test T
JOIN
(
SELECT name,MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
) C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
T.ID
--ranking + CTE
;WITH cOldest AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test
)
SELECT
C.*
FROM
cOldest C
WHERE
C.rnDOB = 1
ORDER BY
C.ID
--ranking + derived table
SELECT
C.*
FROM
(SELECT *,ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test) C
WHERE
C.rnDOB = 1
ORDER BY
C.ID
