关于mysql多表合并查询问题(求sql大师)
自己写的sql语句：主要查询三个表中的 ip 及 出现数量
select a.ip,a.numm FROM (select ip,count() AS numm FROM tableA GROUP BY ip UNION ALl select ip,count() AS numm FROM tableB GROUP BY ip UNION ALl select ip,count(*) AS numm FROM tableC GROUP BY ip) as a WHERE a.ip!='' ORDER BY numm DESC LIMIT 0,10;
结果demo:
192.241.211.44 5
192.241.225.20 4
192.241.225.20 2
192.241.211.44 1
但是结果中会出现同一ip，多个count结果，想要根据ip再合并后面数量该怎么做呢？
想要效果:
192.241.211.44 6
192.241.225.20 6
基于Monster 组和GPT的调写：
SELECT ip, SUM(numm) AS total_numm
FROM (
SELECT ip, COUNT(*) AS numm FROM tableA GROUP BY ip
UNION ALL
SELECT ip, COUNT(*) AS numm FROM tableB GROUP BY ip
UNION ALL
SELECT ip, COUNT(*) AS numm FROM tableC GROUP BY ip
) AS a
WHERE ip != ''
GROUP BY ip
ORDER BY total_numm DESC
LIMIT 0, 10;