27,579
社区成员
发帖
与我相关
我的任务
分享
create table tb(col1 varchar(10),col2 int,col3 int,col4 int,col5 varchar(10),col6 varchar(10))
insert into tb select 'Y',10,5,123,'A','你好'
insert into tb select 'C',15,6,231,'A','你好啊'
insert into tb select 'C',15,5,321,'A','你好吗'
insert into tb select 'F',20,8,111,'A','你真的好'
select * from tb t where not exists(
select 1 from tb where col1=t.col1 and col3>t.col3
)
/*
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
Y 10 5 123 A 你好
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
(3 row(s) affected)
*/
drop table tb
create table tb(col1 varchar(10) , col2 int , col3 int , col4 int , col5 varchar(10) , col6 varchar(10))
insert into tb values('Y', 10 , 5 , 123 , 'A', '你好')
insert into tb values('C', 15 , 6 , 231 , 'A', '你好啊')
insert into tb values('C', 15 , 5 , 321 , 'A', '你好吗')
insert into tb values('F', 20 , 8 , 111 , 'A', '你真的好')
go
--方法1:
select a.* from tb a where col3 = (select max(col3) from tb where col1 = a.col1) order by a.col1
--方法2:
select a.* from tb a where not exists(select 1 from tb where col1 = a.col1 and col3 > a.col3)
--方法3:
select a.* from tb a,(select col1,max(col3) col3 from tb group by col1) b where a.col1 = b.col1 and a.col3 = b.col3 order by a.col1
--方法4:
select a.* from tb a inner join (select col1 , max(col3) col3 from tb group by col1) b on a.col1 = b.col1 and a.col3 = b.col3 order by a.col1
--方法5
select a.* from tb a where 1 > (select count(*) from tb where col1 = a.col1 and col3 > a.col3 ) order by a.col1
drop table tb
/*
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
Y 10 5 123 A 你好
(所影响的行数为 3 行)
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
Y 10 5 123 A 你好
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
(所影响的行数为 3 行)
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
Y 10 5 123 A 你好
(所影响的行数为 3 行)
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
Y 10 5 123 A 你好
(所影响的行数为 3 行)
col1 col2 col3 col4 col5 col6
---------- ----------- ----------- ----------- ---------- ----------
C 15 6 231 A 你好啊
F 20 8 111 A 你真的好
Y 10 5 123 A 你好
(所影响的行数为 3 行)
*/
create table tb(col1 varchar(10),col2 int,col3 int,col4 int,col5 varchar(10),col6 varchar(10))
insert into tb select 'Y',10,5,123,'A','你好'
insert into tb select 'C',15,6,231,'A','你好啊'
insert into tb select 'C',15,5,321,'A','你好吗'
insert into tb select 'F',20,8,111,'A','你真的好'
select * from tb t where not exists(
select 1 from tb where col1=t.col1 and col3>t.col3
)
select * from t1 t where not exists(
select 1 from t1 where col1=t.col1 and col3>t.col3
)
select * from t1 a where col3 = (select max(col3) from tb where col1 = a.col1)
--or:
select * from t1 a where not exists(select 1 from t1 where col1 = a.col1 and col3>a.col3)
--or:
select a.* from t1 a,(select col1,max(col3) col3 from t1 group by col1) b
where a.col1 = b.col1 and a.col3 = b.col3
--or:
select * from t1 a where
(select count(distinct con3) from t1 where col1 = a.col1 and col3 >= a.col3 )=1
select a.* from Tb a inner join
(select Col1,Col2,Max(Col3) as c3 from Tb Group by Col1,Col2) b
on a.Col1 = b.Col1 and a.Col2 = b.Col2 and a.Col3 = b.C3
order by a.Col1,a.Col2,a.Col3
select *
from tb t
where not exists(select 1 from tb where col1=t.col1 and col3>t.col3)