MySQL联合表按列分组

Question

我有两张桌子searches和recommended_searches。两者都有两列id和keyword

搜索表

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

recommended_searches表

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，recommended_search使用recommended_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

Answer 2

你想要的不是一个联合，你想要的是它们之间的连接。

SELECT a.keyword, a.cnt AS cnt, b.cnt AS cnt_recommended
FROM
  (SELECT keyword, count(*) as cnt FROM searches GROUP BY keyword ORDER by cnt DESC) as a
LEFT OUTER JOIN
  (SELECT keyword, count(*) as cnt FROM recommended_searches GROUP BY keyword ORDER by cnt DESC) as b
  ON a.keyword = b.keyword
GROUP BY a.keyword
ORDER BY ...

这将连接两个查询，并分别给出它们的计数。根据您的需求，连接类型可能会有所不同。