导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

取相同username中id最大的行

ltpinpin783 2008-01-21 03:02:09

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)
...全文
138 点赞 收藏 24
写回复
24 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lovehilton 2008-07-15
接分
回复
sp4 2008-01-23
上一个条件写反了,呵呵
Select * From Test a Where Not Exists(
Select 1 From Test Where UserName = a.UserName
And Id> a.Id)
回复
sp4 2008-01-23
Select * From Test a Where Not Exists(
Select 1 From Test Where UserName = a.UserName
And a.Id>Id)
回复
pt1314917 2008-01-22

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

........方法太多,不一一列举了。。。
回复
parss 2008-01-22

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




select * from test a where a.id in (select max(b.id) from test b where a.username=b.username)
回复
penglewen 2008-01-22

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)
回复
wyb0026 2008-01-22
帮顶
回复
fcuandy 2008-01-22
散分贴
回复
bqb 2008-01-22
楼主这贴花了你100分,亏大了!
回复
威尔亨特 2008-01-22
select t.* from Test t where id in (select max(id) from Test where USERNAME =t.USERNAME )
回复
ojuju10 2008-01-21


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
回复
dobear_0922 2008-01-21
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
回复
-狙击手- 2008-01-21
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
回复
Andy-W 2008-01-21
up:
select a.* from test a where not exists(select 1 from test where username = a.username and id > a.id)

回复
pengxuan 2008-01-21
select * from Test t where not exists(select 1 from test where username=t.username and id>t.id)
回复
dawugui 2008-01-21
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

回复
dawugui 2008-01-21
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 行)
*/
回复
zefuzhang2008 2008-01-21

;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
回复
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 行)

回复
拓狼 2008-01-21
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告