22,210
社区成员
发帖
与我相关
我的任务
分享
create table t(字段1 int,字段2 int,字段3 varchar(10))
insert into t values(1 ,201 ,'aa')
insert into t values(1 ,201 ,'bb')
insert into t values(1 ,200 ,'cc')
insert into t values(2 ,301 ,'dd')
insert into t values(2 ,301 ,'ee')
insert into t values(2 ,300 ,'ff')
insert into t values(3 ,401 ,'gg')
insert into t values(3 ,401 ,'hh')
insert into t values(3 ,400 ,'ii')
go
select m.* from t m where not exists(select 1 from t where 字段1=m.字段1 and (字段2 > m.字段2 or (字段2 = m.字段2 and 字段3 < m.字段3))) order by m.字段1
/*
字段1 字段2 字段3
----------- ----------- ----------
1 201 aa
2 301 dd
3 401 gg
(所影响的行数为 3 行)
*/
select 字段1,字段2,字段3 from
(
select m.* , px = (select count(1) from t where 字段1=m.字段1 and (字段2 > m.字段2 or (字段2 = m.字段2 and 字段3 < m.字段3))) + 1 from t m
) k where px = 1
order by 字段1
/*
字段1 字段2 字段3
----------- ----------- ----------
1 201 aa
2 301 dd
3 401 gg
(所影响的行数为 3 行)
*/
drop table t
create table t(字段1 int,字段2 int,字段3 varchar(10))
insert into t values(1 ,201 ,'aa')
insert into t values(1 ,201 ,'bb')
insert into t values(1 ,200 ,'cc')
insert into t values(2 ,301 ,'dd')
insert into t values(2 ,301 ,'ee')
insert into t values(2 ,300 ,'ff')
insert into t values(3 ,401 ,'gg')
insert into t values(3 ,401 ,'hh')
insert into t values(3 ,400 ,'ii')
go
select m.* from t m where not exists(select 1 from t where 字段1=m.字段1 and (字段2 > m.字段2 or (字段2 = m.字段2 and 字段3 < m.字段3))) order by m.字段1
/*
字段1 字段2 字段3
----------- ----------- ----------
1 201 aa
2 301 dd
3 401 gg
(3 行受影响)
*/
select 字段1,字段2,字段3 from
(
select m.* , px = row_number() over(partition by 字段1 order by 字段2 desc , 字段3) from t m
) k where px = 1
order by 字段1
/*
字段1 字段2 字段3
----------- ----------- ----------
1 201 aa
2 301 dd
3 401 gg
(3 行受影响)
*/
drop table t
create table t(字段1 int,字段2 int,字段3 varchar(10))
insert into t values(1 ,201 ,'aa')
insert into t values(1 ,201 ,'bb')
insert into t values(1 ,200 ,'cc')
insert into t values(2 ,301 ,'dd')
insert into t values(2 ,301 ,'ee')
insert into t values(2 ,300 ,'ff')
insert into t values(3 ,401 ,'gg')
insert into t values(3 ,401 ,'hh')
insert into t values(3 ,400 ,'ii')
go
select m.* from t m where not exists(select 1 from t where 字段1=m.字段1 and (字段2 > m.字段2 or (字段2 = m.字段2 and 字段3 < m.字段3))) order by m.字段1
drop table t
/*
字段1 字段2 字段3
----------- ----------- ----------
1 201 aa
2 301 dd
3 401 gg
(所影响的行数为 3 行)
*/