一位网友在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 ]);
执行结果: