检索最新记录每个客户

问题描述：

ID NAME DATE 
3 JOHN 2011-08-08 
2 YOKO 2010-07-07 
1 JOHN 2009-06-06

码（用于SQL Server 2005）：

DECLARE @TESTABLE TABLE (id int, name char(4), date smalldatetime) 
INSERT INTO @TESTABLE VALUES (3, 'JOHN', '2011-08-08') 
INSERT INTO @TESTABLE VALUES (2, 'YOKO', '2010-07-07') 
INSERT INTO @TESTABLE VALUES (1, 'JOHN', '2009-06-06')

我想，每一个名称，编号有最近的DATE。像这样：

3 JOHN 2011-08-08 
2 YOKO 2010-07-07

完成此操作的最优雅方式是什么？

答

;WITH x AS 
(
    SELECT ID, NAME, [DATE], 
     rn = ROW_NUMBER() OVER 
     (PARTITION BY NAME ORDER BY [DATE] DESC) 
    FROM @TESTABLE 
) 
SELECT ID, NAME, [DATE] FROM x WHERE rn = 1 
    ORDER BY [DATE] DESC;

尽量避免保留字（和模糊的列名）像 ...

+1对于保留字谨慎 – JNK

这是优雅的。它比JOIN 2版本更高效（更快）吗？ –

@idevlop - 我认为速度将取决于您的索引而不是查询。我认为这与其他版本之间的差异应该是最小的性能。 – JNK

答

SELECT <fields> 
FROM SourceTable st 
INNER JOIN (SELECT name, MAX(Datefield) as Datefield 
      FROM SourceTable 
      GROUP BY name) x 
    ON x.Name = st.name 
    AND x.datefield = st.datefield

答

如果源表是标准化（例如：一个表，为客户和另一个表订单），可以使用CROSS APPLY这样的：

DECLARE @CUSTOMER TABLE(id int primary key, name char(4)); 
INSERT INTO @CUSTOMER VALUES (3, 'JOHN'); 
INSERT INTO @CUSTOMER VALUES (2, 'YOKO'); 
INSERT INTO @CUSTOMER VALUES (1, 'JOHN'); 


DECLARE @ORDER TABLE (orderid int identity(1,1) primary key, customerid int, [date] smalldatetime, UNIQUE(customerid,[date] DESC,orderid)) ; 
INSERT INTO @ORDER VALUES (3, '2011-08-08'); 
INSERT INTO @ORDER VALUES (2, '2010-07-07'); 
INSERT INTO @ORDER VALUES (1, '2009-06-06'); 

SELECT * 
FROM @CUSTOMER c 
CROSS APPLY 
(
    SELECT TOP 1 o.* 
    FROM @ORDER o 
    WHERE o.customerid = c.id 
    ORDER BY o.[date], o.orderid DESC 
) ca

有了正确的索引（UNIQUE(customerid,[date] DESC,orderid)），你可以得到一个不错的表现。在这个例子中，我使用了一个UNIQUE约束来定义一个表变量的非聚集索引来加速CROSS APPLY子查询。

检索最新记录每个客户

相关推荐