取相同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)
...全文
206 24 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
昵称被占用了 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
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
加载更多回复(4)

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧