Question

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

假设数据库里有两个表：AAAA和BBBB
AAAA中三列分别是：
id ab tt
1 123 01
1 234 02
1 345 03
1 456 04
BBBB中两列分别是：
id ab tt
2 128 01
2 233 02
2 341 03
2 459 04
现在需要统计出AAAA和BBBB在同一个tt时ab的最大值及其对应的id，比如查询tt=02时结果为
id ab
1 234
SQL语句该怎么写呢？非常感谢

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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