MySQL UNION 表按列分组

Question

我有两个表searches 和recommended_searches。两者都有两列 id 和 keyword

搜索表

id | keyword 
1  | one 
2  | two 
3  | one 
4  | one 
5  | two
6  | one

推荐搜索表

id | key 
1  | two 
2  | two 
3  | one 
4  | two 
5  | two
6  | one

我使用此查询返回搜索表中每个关键字的计数

SELECT keyword, count(*) as cnt FROM searches GROUP BY keyword ORDER by cnt DESC

这个查询是推荐的

SELECT keyword, count(*) as cnt FROM recommended_searches GROUP BY keyword ORDER by cnt DESC

我想在这两个表上按键进行分组，但有用于搜索表的 cnt 和用于推荐搜索的 recommished_cnt。这样我就可以根据表类型进行排序，例如按推荐搜索排序。

SELECT keyword, count(*) as cnt FROM searches
UNION ALL
SELECT keyword, count(*) as recommended_cnt FROM recommended_searches
GROUP BY keyword ORDER by recommended_cnt DESC

如何同时返回 cnt 和 recommended_cnt？我尝试了上面的方法，但没有成功。

我想要一个像

这样的结果

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

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

Answer 1

将计数放入两个子查询的不同列中。

SELECT `key`, MAX(cnt) AS cnt, MAX(recommended_cnt) AS recommended_cnt
FROM (
    SELECT `keyword` AS `key`, COUNT(*) AS cnt, 0 AS recommended_cnt
    FROM searches
    GROUP BY `key`
    UNION ALL
    SELECT `key`, 0 AS cnt, COUNT(*) AS recommended_cnt
    FROM recommended_searches
    GROUP BY `key`
) AS x
GROUP BY `key`

DEMO