SELECT * FROM tb_name t1 WHERE EXISTS (SELECT t2.a, max(t2.b) FROM tb_name t2 WHERE t2.a=t1.a GROUP BY t2.a HAVING max(t2.b)=t1.b );
楼上正解 with Table_A as (select 'a1' a ,1 b from dual union all select 'a1' a ,2 b from dual union all select 'a1' a ,3 b from dual union all select 'a2' a ,1 b from dual union all select 'a2' a ,2 b from dual union all select 'a3' a ,1 b from dual union all select 'a4' a ,2 b from dual
)SELECT * FROM Table_A t1 WHERE EXISTS (SELECT t2.a, max(t2.b) FROM Table_A t2 WHERE t2.a=t1.a GROUP BY t2.a) HAVING max(t2.b)=t1.b );
select * from tb t where exists(select 1 from tb where t.a=a and t.b>b)select * from tb where a,b in(select a,max(b) from tb group by a)
select t1.* ,t2.maxb from table t1,(select a ,max(b) as maxb from table group by a) t2 where t1.a=t2.a;
from tb
group by a
列很多。
我想写 select * 呢?
FROM tb_name t1
WHERE EXISTS (SELECT t2.a, max(t2.b)
FROM tb_name t2
WHERE t2.a=t1.a
GROUP BY t2.a
HAVING max(t2.b)=t1.b );
with Table_A as (select 'a1' a ,1 b from dual
union all
select 'a1' a ,2 b from dual
union all
select 'a1' a ,3 b from dual
union all
select 'a2' a ,1 b from dual
union all
select 'a2' a ,2 b from dual
union all
select 'a3' a ,1 b from dual
union all
select 'a4' a ,2 b from dual
)SELECT *
FROM Table_A t1
WHERE EXISTS (SELECT t2.a, max(t2.b)
FROM Table_A t2
WHERE t2.a=t1.a
GROUP BY t2.a)
HAVING max(t2.b)=t1.b );
select * from tb t where exists(select 1 from tb where t.a=a and t.b>b)select * from tb where a,b in(select a,max(b) from tb group by a)