3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
取相同username中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'
要的结果是:
ID USERNAME A B
3 wlh aaa bbbb
4 lsl aaaa bb
=====================================================
相同username的取max(id)
...全文
当前发帖距今超过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)
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)
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 testAndy-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)
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
