取相同username中id最大的行

[ltpinpin783]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 

要的结果是:
ID USERNAME A B
3 wlh aaa bbbb
4 lsl aaaa bb
=====================================================
相同username的取max(id)

[拓狼]

select * from test a where not exists(select 1 from test b where a.username=b.username and a.id<b.id)

---------------------------------------------------------------
更多内容，详见微软BI开拓者www.windbi.com

[昵称被占用了]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 

select * from test a
where not exists (
select 1 from test
where USERNAME=a.USERNAME
and id>a.id
)

--结果
id          username                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）

[zefuzhang2008]

;with t as(
select id,username,a,b,row_number() over(PARTITION BY username order by id desc) as rowid
from test)
select id,username,a,b
from t
where rowid=1

[dawugui]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 
--方法1：
select a.* from test a where id = (select max(id) from test where username = a.username) order by a.username
--方法2：
select a.* from test a where not exists(select 1 from test where username = a.username and id > a.id)
--方法3：
select a.* from test a,(select username,max(id) id from test group by username) b where a.username = b.username and a.id = b.id order by a.username
--方法4：
select a.* from test a inner join (select username , max(id) id from test group by username) b on a.username = b.username and a.id = b.id order by a.username
--方法5
select a.* from test a where 1 > (select count(*) from test where username = a.username and id > a.id ) order by a.username

drop table test

/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/

[dawugui]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 
--方法1：
select a.* from test a where id = (select max(id) from test where username = a.username) order by a.username
/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
--方法2：
select a.* from test a where not exists(select 1 from test where username = a.username and id > a.id)
/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
--方法3：
select a.* from test a,(select username,max(id) id from test group by username) b where a.username = b.username and a.id = b.id order by a.username
/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
--方法4：
select a.* from test a inner join (select username , max(id) id from test group by username) b on a.username = b.username and a.id = b.id order by a.username
/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
--方法5
select a.* from test a where 1 > (select count(*) from test where username = a.username and id > a.id ) order by a.username
/*
id          userusername                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
drop table test

[pengxuan]

select * from Test t where not exists(select 1 from test where username=t.username and id>t.id)

[Andy-W]

up:
select a.* from test a where not exists(select 1 from test where username = a.username and id > a.id)

[-狙击手-]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 

select *
from test t
where not exists( select 1 from test where t.username = username and id > t.id)
/*

id          username                                                                                                                                                                                                                                                        A                    B                    
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- 
3           wlh                                                                                                                                                                                                                                                             aaa                  bbbb
4           lsl                                                                                                                                                                                                                                                             aaaa                 bb

（所影响的行数为 2 行）
*/
drop table test

[dobear_0922]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb'
 
select * from test a where not exists(select 1 from test b where a.username=b.username and a.id<b.id)
/*
id          username          
----------- ------------------       
3           wlh 
4           lsl  

(2 row(s) affected)
*/

drop table test

[ojuju10]

create table Test (id int,username varchar(255),A varchar(20),B varchar(20))
insert into Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 

--1
select * from test a
where not exists(select 1 from test b where a.username=b.username and a.id<b.id)

--2
select * from test a
where id in (select max(id) from test b where a.username=b.username )

--3
select a.* from test a,(select username,max(id) as id  from test  group by username ) b
where a.username=b.username and a.id=b.id

[威尔亨特]

select t.* from Test t where id in (select max(id) from Test where USERNAME =t.USERNAME )

[bqb]

楼主这贴花了你100分，亏大了！

[fcuandy]

散分贴

[wyb0026]

帮顶

[penglewen]

declare @Test table (id int,username varchar(255),A varchar(20),B varchar(20))
insert into @Test
select 1,'wlh','aa','bb' union
select 2,'wlh','aaa','bbb' union
select 3,'wlh','aaa','bbbb' union
select 4,'lsl','aaaa','bb' 
select * from @test where id in(
select max(id) from @Test group by username)

[parss]

这个我用的多，拿出来分享一下吧

select * from test a where a.id in (select max(b.id) from test b where a.username=b.username)

[pt1314917]

select * from test a where id in(select max(id) from test where username=a.username)
--或者:
select * from test a where id in(select top 1 id from test where username=a.username order by id)
--或者
select * from test a where
not exists(select 1 from test where username=a.username and id>a.id)
---又或者
select * from test a
where (select count(1) from test where username=a.username and id>=a.id)=1

........方法太多，不一一列举了。。。

[sp4]

Select * From Test a Where Not Exists(
Select 1 From Test Where UserName = a.UserName
And a.Id>Id)

[sp4]

上一个条件写反了，呵呵
Select * From Test a Where Not Exists(
Select 1 From Test Where UserName = a.UserName
And Id> a.Id)

[lovehilton]

接分