56,677
社区成员
发帖
与我相关
我的任务
分享
drop table if exists t;
create table t(
A int,
B nvarchar(10),
C datetime
);
insert into t values(1,'张三','2015-01-01');
insert into t values(2,'李四','2016-01-01');
insert into t values(1,'李四','2015-02-01');
insert into t values(2,'张三','2017-02-01');
select t.* from t inner join (
select A,max(C) as maxC from t group by A
) as tt
on t.A=tt.A and t.C=tt.maxC;
/*
+---+------+---------------------+
| A | B | C |
+---+------+---------------------+
| 1 | 李四 | 2015-02-01 00:00:00 |
| 2 | 张三 | 2017-02-01 00:00:00 |
+---+------+---------------------+
*/
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] int,[B] nvarchar(22),[C] Date)
Insert #T
select 1,N'张三','2015-1-1' union all
select 2,N'李四','2016-1-1' union all
select 1,N'李四','2015-2-1' union all
select 2,N'张三','2017-2-1'
Go
--测试数据结束
SELECT a.*
FROM #T a
JOIN
(SELECT A, MAX(C) c FROM #T GROUP BY A) t
ON t.A = a.A
AND t.c = a.C;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] int,[B] nvarchar(22),[C] Date)
Insert #T
select 1,N'张三','2015-1-1' union all
select 2,N'李四','2016-1-1' union all
select 1,N'李四','2015-2-1' union all
select 2,N'张三','2017-2-1'
Go
--测试数据结束
SELECT a.*
FROM #T a
JOIN
(SELECT B, MAX(C) c FROM #T GROUP BY B) t
ON t.B = a.B
AND t.c = a.C;