重复记录的取最新一笔
一位网友在Insus.NET下班时问及一个SQL问题，他有如下一些记录：
S_NUMBER P_NO UPDATE_TIME
Y1202022524 E3465 2012-02-05 16:32:16.000
Y1202022524 E0035 2012-01-16 05:38:41.000
Y1202022525 E0036 2012-01-17 05:38:41.000
Y1202022526 E0037 2012-01-18 05:38:41.000
Y1202022527 E0038 2012-01-19 05:38:41.000
Y1202022528 E0039 2012-01-20 05:38:41.000
Y1202022529 E0040 2012-01-21 05:38:41.000
要SELECT所有记录，过滤所有重复[S_NUMBER]的记录，只取[UPDATE_TIME]最新的一笔。
下面是Insus.NET帮他解决的方法与过程：
创建一个临时表#Data
CREATE TABLE [#Datas]
(
S_NUMBER NVARCHAR(30),
P_NO NVARCHAR(30),
UPDATE_TIME DATETIME
)
(
S_NUMBER NVARCHAR(30),
P_NO NVARCHAR(30),
UPDATE_TIME DATETIME
)
把数据插入这个临时表中：
View Code
INSERT INTO [#Datas] VALUES
('Y1202022524', 'E3465', '2012/02/05 16:32:16'),
('Y1202022524', 'E0035', '2012/01/16 05:38:41'),
('Y1202022525', 'E0036', '2012/01/17 05:38:41'),
('Y1202022526', 'E0037', '2012/01/18 05:38:41'),
('Y1202022527', 'E0038', '2012/01/19 05:38:41'),
('Y1202022528', 'E0039', '2012/01/20 05:38:41'),
('Y1202022529', 'E0040', '2012/01/21 05:38:41')
GO
('Y1202022524', 'E3465', '2012/02/05 16:32:16'),
('Y1202022524', 'E0035', '2012/01/16 05:38:41'),
('Y1202022525', 'E0036', '2012/01/17 05:38:41'),
('Y1202022526', 'E0037', '2012/01/18 05:38:41'),
('Y1202022527', 'E0038', '2012/01/19 05:38:41'),
('Y1202022528', 'E0039', '2012/01/20 05:38:41'),
('Y1202022529', 'E0040', '2012/01/21 05:38:41')
GO
下面SELECT是在MS SQL Server 2008 R2查询分析器实现：
View Code
WITH it([S_NUMBER],[UPDATE_TIME])
AS
(
SELECT [S_NUMBER],MAX([UPDATE_TIME]) FROM [dbo].[#Datas] GROUP BY [S_NUMBER]
)
SELECT DISTINCT d.[S_NUMBER],[P_NO],d.[UPDATE_TIME]
FROM [dbo].[#Datas] AS d
INNER JOIN it
ON (d.[S_NUMBER] = it.[S_NUMBER] and d.[UPDATE_TIME] = it.[UPDATE_TIME]);
AS
(
SELECT [S_NUMBER],MAX([UPDATE_TIME]) FROM [dbo].[#Datas] GROUP BY [S_NUMBER]
)
SELECT DISTINCT d.[S_NUMBER],[P_NO],d.[UPDATE_TIME]
FROM [dbo].[#Datas] AS d
INNER JOIN it
ON (d.[S_NUMBER] = it.[S_NUMBER] and d.[UPDATE_TIME] = it.[UPDATE_TIME]);
执行结果：
