ID int,
name varchar(10),
age int
);
go
-- 用于测试 楼主的原始数据.
INSERT INTO #test
SELECT 1, 'tom', 22 UNION ALL
SELECT 2, 'tom', 23 UNION ALL
SELECT 3, 'tom', 24 UNION ALL
SELECT 4, 'lily', 22 UNION ALL
SELECT 5, 'lily', 23;
-- 用于测试 年龄不是按照大小顺序
INSERT INTO #test
SELECT 101, '张三', 23 UNION ALL
SELECT 102, '张三', 22 UNION ALL
SELECT 103, '张三', 21;
GO
-- 楼主的SQL
select * from #test where ID in(select max(t.ID) from #test t group by t.NAME)
GO
ID name age
----------- ---------- -----------
3 tom 24
5 lily 23
103 张三 21
(3 行受影响)
-- 方法1：
SELECT
*
FROM
#test main
WHERE
NOT EXISTS( select 1 FROM #test sub where main.name=sub.name AND main.age
ID name age
----------- ---------- -----------
3 tom 24
5 lily 23
101 张三 23
(3 行受影响)
-- 方法2：
select * from #test
where name + '.' + CAST(age as char) IN (SELECT name + '.' + CAST(MAX(age) as char) FROM #test GROUP by name);
GO
ID name age
----------- ---------- -----------
3 tom 24
5 lily 23
101 张三 23
(3 行受影响)
