MySQL多表联合统计：获取searches与推荐表的关键词计数

没问题，我来帮你搞定这个需求！你之前的UNION ALL写法之所以无效，是因为它把两张表的统计结果混在一起了，没办法分别保留各自的计数。下面给你两种可靠的实现方式，都能得到你想要的结果：

方法一：先分别统计再关联（推荐，性能更优） #

先对两张表各自分组统计关键词数量，再通过**全连接（FULL JOIN）**合并结果，确保所有关键词都被覆盖：

SELECT
    -- 取两张表中存在的关键词，避免某张表没有该关键词时出现NULL
    COALESCE(s.keyword, rs.keyword) AS keyword,
    -- 若某张表无该关键词，计数默认0
    COALESCE(s.cnt, 0) AS cnt,
    COALESCE(rs.recommended_cnt, 0) AS recommended_cnt
FROM
    -- 统计searches表的关键词数量
    (SELECT keyword, COUNT(*) AS cnt FROM searches GROUP BY keyword) s
FULL JOIN
    -- 统计recommended_searches表，注意把`key`重命名为keyword方便关联
    (SELECT `key` AS keyword, COUNT(*) AS recommended_cnt FROM recommended_searches GROUP BY `key`) rs
ON s.keyword = rs.keyword
-- 按你需要的列排序，这里是recommended_cnt降序
ORDER BY recommended_cnt DESC;

方法二：合并数据后条件聚合 #

先把两张表的所有关键词数据合并并标记来源，再通过条件聚合分别统计各自的数量：

SELECT
    keyword,
    -- 统计来自searches表的数量
    SUM(CASE WHEN source = 'searches' THEN 1 ELSE 0 END) AS cnt,
    -- 统计来自recommended_searches表的数量
    SUM(CASE WHEN source = 'recommended' THEN 1 ELSE 0 END) AS recommended_cnt
FROM
    -- 合并两张表的数据，添加来源标记
    (SELECT keyword, 'searches' AS source FROM searches
     UNION ALL
     SELECT `key` AS keyword, 'recommended' AS source FROM recommended_searches) combined
GROUP BY keyword
ORDER BY recommended_cnt DESC;

为什么你之前的写法不对？ #

你原来的UNION ALL查询会把两张表的统计结果（都是keyword和cnt列）合并到一起，然后再分组，这样得到的是两张表所有关键词的总计数，而不是分别统计各自的数量，自然得不到你想要的结果。

用上面任意一种方法，都能得到你期望的输出：

Array( [keyword] => one [cnt] => 4 [recommended_cnt] => 2 ); Array( [keyword] => two [cnt] => 2 [recommended_cnt] => 4 );

内容的提问来源于stack exchange，提问作者user892134