select * from tb t where 编号=(select max(编号) from tb where 姓名=t.姓名)
select * from tb t where not exists(select * from tb where t.姓名=姓名 and 编号>t.编号)
create table ceshi (id int,name varchar(10),money int) insert ceshi select 1 , '张三' , 500 union all select 2 , '张三' , 300 union all select 3 , '李四' , 1000 union all select 4 , '张三' , 3000 union all select 5 , '张三' , 600 union all select 6 , '李四' , 609 union all select 7 , '王五' , 0 -----select * from ceshi select * from ceshi a where not exists(select 1 from ceshi where name=a.name and id>a.id )
create table t(id int identity(1,1),[name] varchar(10),feiyong money)insert into t select '张三', 500 union all select '张三', 300 union all select '李四', 1000 union all select '张三', 3000 union all select '张三', 600 union all select '李四', 609 union all select '王五', 0 /*------------------------ select * from t a where not exists(select 1 from t where [name]=a.[name] and id>a.id) ------------------------*/ id name feiyong ----------- ---------- --------------------- 5 张三 600.00 6 李四 609.00 7 王五 0.00(3 行受影响)
declare @a table (编号 int identity(1,1),姓名 nvarchar(32),费用 money) insert into @a select N'张三', 500 union all select N'张三', 300 union all select N'李四', 1000 union all select N'张三', 3000 union all select N'张三', 600 union all select N'李四', 609 union all select N'王五', 0 select 姓名,费用 from @a where 编号 in (select 编号 from (select max(编号) as 编号,姓名 from @a group by 姓名) as b)
from tb t
where not exists(select * from tb where t.姓名=姓名 and 编号>t.编号)
insert ceshi
select 1 , '张三' , 500 union all
select 2 , '张三' , 300 union all
select 3 , '李四' , 1000 union all
select 4 , '张三' , 3000 union all
select 5 , '张三' , 600 union all
select 6 , '李四' , 609 union all
select 7 , '王五' , 0 -----select * from ceshi
select * from ceshi a where not exists(select 1 from ceshi where name=a.name and id>a.id )
select '张三', 300 union all
select '李四', 1000 union all
select '张三', 3000 union all
select '张三', 600 union all
select '李四', 609 union all
select '王五', 0
/*------------------------
select * from t a where not exists(select 1 from t where [name]=a.[name] and id>a.id)
------------------------*/
id name feiyong
----------- ---------- ---------------------
5 张三 600.00
6 李四 609.00
7 王五 0.00(3 行受影响)
insert into @a select
N'张三', 500 union all select
N'张三', 300 union all select
N'李四', 1000 union all select
N'张三', 3000 union all select
N'张三', 600 union all select
N'李四', 609 union all select
N'王五', 0
select 姓名,费用 from @a where 编号 in (select 编号 from (select max(编号) as 编号,姓名 from @a group by 姓名) as b)