上面写错了 select top 1 * from table where type=1 union select top 1 * from table where type=2 union select top 1 * from table where type=3
如果有标识列:select * from 表 t where not exists(select 1 from 表 where type=t.type and 标识>t.标识)
declare @tb table(ID int identity,type int,name varchar(10)) insert @tb(type,name) select 1,'AAA' union select 1,'BBB' union select 2,'CCC' union select 2,'DDD' union select 3,'EEE' union select 3,'GGG' union select 4,'HHH' --测试 select * from @tb t where not exists(select 1 from @tb where type=t.type and ID>t.ID)--结果 /* ID type name ----------- ----------- ---------- 2 1 BBB 4 2 DDD 6 3 GGG 7 4 HHH(所影响的行数为 4 行) */
select top 1 * from table where type=1
union
select top 1 * from table where type=2
union
select top 1 * from table where type=3
where not exists(select 1 from 表 where type=t.type and 标识>t.标识)
insert @tb(type,name)
select 1,'AAA' union
select 1,'BBB' union
select 2,'CCC' union
select 2,'DDD' union
select 3,'EEE' union
select 3,'GGG' union
select 4,'HHH' --测试
select * from @tb t
where not exists(select 1 from @tb where type=t.type and ID>t.ID)--结果
/*
ID type name
----------- ----------- ----------
2 1 BBB
4 2 DDD
6 3 GGG
7 4 HHH(所影响的行数为 4 行)
*/