php - MySQL如何统计同一个表的多个列中出现的一次？

Question

我希望计算表格中 5 列之间最常使用的前 5 个关键字。

所以我的表是站点，我的列是“site_motclef1”，“site_motclef2”，“site_motclef3”，“site_motclef4”和“site_motclef5”

我知道如何一列查找最多使用的关键字，但我想计算所有 5 列中的总数。

这是我发现仅在一列中计数的方式 =>

SELECT COUNT( * ) total, site_motclef1
FROM sites
WHERE  `site_active` =  'Yes'
GROUP BY site_motclef1
ORDER BY  `total` DESC 
LIMIT 0 , 5

有人可以开车送我吗？

谢谢:)

#

我正在尝试这种方式，但这并没有给我一个正确的结果:/也许我的状态很好？

select count(*) total, site_motclef
from (
  select site_motclef1 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION
  select site_motclef2 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION
  select site_motclef3 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION
  select site_motclef4 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION
  select site_motclef5 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
) AS X
ORDER BY total DESC

Answer 1

虽然我完全同意 Juergen 的观点，即您应该通过为每个关键字和站点设置一个单独的表来修复数据库设计，但您的查询的问题是 union 和缺少 分组依据。它正在删除重复项。您需要将其替换为union all:

select count(*) total, site_motclef
from (
  select site_motclef1 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION ALL
  select site_motclef2 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION ALL
  select site_motclef3 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION ALL
  select site_motclef4 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
  UNION ALL
  select site_motclef5 as site_motclef FROM sites WHERE  `site_active` =  'Yes'
) AS X
group by site_motclef
ORDER BY total DESC;

如果您只想要 5 个，请在末尾添加 limit 5。