--1 SELECT * FROM TB T1 WHERE NOT EXISTS (SELECT 1 FROM TB T2 WHERE T1.ID=T2.ID AND T1.Name=T2.Name AND CONVERT(VARCHAR(8),T1.[DateTime],108)>CONVERT(VARCHAR(8),T2.[DateTime],108)) --2 SELECT T1.* FROM TB T1 LEFT JOIN TB T2 ON T1.ID=T2.ID AND T1.Name=T2.Name AND CONVERT(VARCHAR(8),T1.[DateTime],108)>CONVERT(VARCHAR(8),T2.[DateTime],108) WHERE T2.ID IS NULL --3 SELECT * FROM( SELECT ROW_NUMBER()OVER(PARTITION BY ID,Name ORDER BY CONVERT(VARCHAR(8),[DateTime],108))RN ,* FROM TB )T WHERE RN=1
create table T ( id int, name nvarchar(10), mydate datetime ) insert into T(id,name,mydate )values(1,'A','2014-10-11 23:47:36') insert into T(id,name,mydate )values(1,'A','2015-10-11 07:35:36') insert into T(id,name,mydate )values(1,'A','2015-10-12 06:24:36')insert into T(id,name,mydate )values(2,'B','2014-11-11 18:29:36') insert into T(id,name,mydate )values(2,'B','2014-04-11 09:27:36') GO select * from ( SELECT id,name,mydate,row_number() over(partition by id,name order by CONVERT(nvarchar(100),mydate,108) )num FROM T )x where num=1
SELECT * FROM TB T1
WHERE NOT EXISTS
(SELECT 1 FROM TB T2
WHERE T1.ID=T2.ID AND T1.Name=T2.Name
AND CONVERT(VARCHAR(8),T1.[DateTime],108)>CONVERT(VARCHAR(8),T2.[DateTime],108))
--2
SELECT T1.* FROM TB T1
LEFT JOIN TB T2 ON T1.ID=T2.ID AND T1.Name=T2.Name
AND CONVERT(VARCHAR(8),T1.[DateTime],108)>CONVERT(VARCHAR(8),T2.[DateTime],108)
WHERE T2.ID IS NULL
--3
SELECT * FROM(
SELECT ROW_NUMBER()OVER(PARTITION BY ID,Name ORDER BY CONVERT(VARCHAR(8),[DateTime],108))RN
,*
FROM TB
)T
WHERE RN=1
(
id int,
name nvarchar(10),
mydate datetime
)
insert into T(id,name,mydate )values(1,'A','2014-10-11 23:47:36')
insert into T(id,name,mydate )values(1,'A','2015-10-11 07:35:36')
insert into T(id,name,mydate )values(1,'A','2015-10-12 06:24:36')insert into T(id,name,mydate )values(2,'B','2014-11-11 18:29:36')
insert into T(id,name,mydate )values(2,'B','2014-04-11 09:27:36')
GO
select * from
(
SELECT id,name,mydate,row_number() over(partition by id,name order by CONVERT(nvarchar(100),mydate,108) )num FROM T
)x where num=1
SELECT ID,MAX(Name),MAX(CONVERT(varchar(100),DateTime,108)) FROM Table
GROUP BY ID
这样就行了