sql查询最大值及其id的方法

SELECT b.ID, b.ab
FROM (SELECT ID, ab, tt
FROM aaaa
UNION ALL
SELECT ID, ab, tt
FROM bbbb) b
WHERE EXISTS (SELECT 1
FROM (SELECT a.tt, MAX (a.ab) ab
FROM (SELECT ID, ab, tt
FROM aaaa
UNION ALL
SELECT ID, ab, tt
FROM bbbb) a
GROUP BY a.tt) c
WHERE c.tt = b.tt AND c.ab = b.ab)
AND b.tt = '02'

最后一行替换你想查的tt

可以用union all连接两个查询，分别查出两种表对应的id和ab的值，然后外层嵌套sql（按ab降序排序，取第一行数据）

SELECT id,max(ab) as ab
from
(
select * from AAAA
union
select * from BBBB)
where tt='02'
group by id

select case when a.ab>b.ab then a.id else b.id end as id
,case when a.ab>b.ab then a.ab else b.ab end as ab
from aaaa as a inner join bbbb as b on a.tt=b.tt

SELECT b.id,b.tt,MAX(ab)as ab
FROM (SELECT ID, ab, tt
FROM aaaa
UNION ALL
SELECT ID, ab, tt
FROM bbbb) b
where tt='02'
group by b.ID,tt