22,301
社区成员




tb
-----------------------
fA fB fC
----- ------ ------
AAA 1 1
AAA 2 2
BBB 1 1
BBB 3 3
CCC 1 1
CCC 2 1
当fA相同时,取fB最大的记录,可以这样写:
select * from tb a where not exists(select 1 from tb b where a.fA = b.fA and a.fB < b.fB)
现在表里新加了一条数据如下
CCC 2 2
当fA相同时,取fB最大的记录,当fA,fB都相同时,取fC最小的记录,SQL语句应该怎么写?
谢谢高手指点
select * from tb a where not exists(select 1 from tb b where (a.fA = b.fA and a.fB<b.fB) OR (a.fA = b.fA And a.fb= b.fB And a.fC<b.fC)
select * from tb a where not exists(select 1 from tb b where (a.fA = b.fA and a.fB) OR (a.fA = b.fA And a.fb= b.fB And a.fC<b.fC)
---少了个括号
--精减下代码
select
*
from
tb a
where
not exists(select 1 from tb b where a.fA = b.fA and (a.fB<b.fB OR a.fb= b.fB And a.fC>b.fC))
--精减下代码
select
*
from
tb a
where
not exists(select 1 from tb b where a.fA = b.fA and (a.fB<b.fB OR a.fb= b.fB And a.fC>b.fC)