MySQL统计多列彩票数字总出现次数并合并输出的查询方法

把6个号码列的所有数据合并为单列数据集后统一分组统计即可，仅需一条SQL即可完成需求。

基础统计查询（返回数字和次数两个字段） #

SELECT 
  num, 
  COUNT(*) AS total_count
FROM (
  SELECT number1 AS num FROM lotto_numbers
  UNION ALL
  SELECT number2 AS num FROM lotto_numbers
  UNION ALL
  SELECT number3 AS num FROM lotto_numbers
  UNION ALL
  SELECT number4 AS num FROM lotto_numbers
  UNION ALL
  SELECT number5 AS num FROM lotto_numbers
  UNION ALL
  SELECT number6 AS num FROM lotto_numbers
) AS all_numbers
GROUP BY num
ORDER BY num ASC;

直接输出指定格式的查询 #

如果需要直接返回1: 435这类拼接好的字符串，使用CONCAT函数即可：

SELECT 
  CONCAT(num, ': ', COUNT(*)) AS result
FROM (
  SELECT number1 AS num FROM lotto_numbers
  UNION ALL
  SELECT number2 AS num FROM lotto_numbers
  UNION ALL
  SELECT number3 AS num FROM lotto_numbers
  UNION ALL
  SELECT number4 AS num FROM lotto_numbers
  UNION ALL
  SELECT number5 AS num FROM lotto_numbers
  UNION ALL
  SELECT number6 AS num FROM lotto_numbers
) AS all_numbers
GROUP BY num
ORDER BY num ASC;

说明 #

• 使用UNION ALL而非UNION：UNION会自动对结果去重，会导致统计次数少于实际值，UNION ALL会保留所有重复值，统计结果准确且执行效率更高。
• 最终结果默认按彩票数字从小到大排序，和你给出的示例输出顺序一致。
• 如果有某数字从未出现过，结果中不会有对应的行，如果你需要补全所有可能的数字（比如彩票数字范围是1-50的话），可以额外关联一个包含所有可选数字的辅助表即可。

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