34,588
社区成员
发帖
与我相关
我的任务
分享
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 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
........方法太多,不一一列举了。。。
select * from test a where a.id in (select max(b.id) from test b where a.username=b.username)
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)
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
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
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
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
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 行)
*/
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 行)
select * from test a where not exists(select 1 from test b where a.username=b.username and a.id<b.id)