SQL彩票查询

Question

我有一个由7列组成的简单表格

Week ¦ 1st ¦ 2nd ¦ 3rd ¦ 4th ¦ 5th ¦ 6th ¦

每周，我父亲都会在我创建的一个简单的PHP脚本中添加星期六英国彩票号码。他有早发的老年痴呆症，并试图保持他的大脑活跃。今晚他问了我一个关于数据库的问题。他问我是否有可能看到6个最受欢迎的数字。

我尝试创建一个简单的SQL查询：

SELECT 1st, 2nd, 3rd, 4th, 5th, 6th, COUNT(*) AS 'foo' FROM `dad` GROUP BY 1st, 2nd, 3rd, 4th, 5th, 6th ORDER BY foo DESC

但结果和我预期的不一样。

1st 2nd 3rd 4th 5th 6th foo
2   6   8   32  33  35  1
3   6   12  17  35  40  1
3   6   31  43  46  53  1
etc

我希望该表合并成一个列，然后计数并得到一个简单的结果，如下所示：

Number   Count
2        1
3        2
6        3
8        1

然后把它按升序排列。然后，我可以使用该SQL查询为他创建一个简单的表，以显示最常见的数字。

我正在考虑做一个通用的SQL查询

SELECT 1st FROM `dad`

然后创建一个包含结果的数组，然后添加

SELECT 2nd FROM `dad`

到Array的末尾，并对所有6列继续，然后使用PHP单独计算数字。

有更快的路吗？

Answer 1

你可以：

select
  n, cnt
from (
  select
    n,
    count(*) as cnt
  from (
    select `1st` as n from `dad` union all
    select `2nd` from `dad` union all
    select `3rd` from `dad` union all  
    select `4th` from `dad` union all
    select `5th` from `dad` union all
    select `6th` from `dad`
  ) x
  group by n
) y
order by cnt desc
limit 6

Answer 2

您的第一步应该是修复您的数据模型。每个数字应该存储在单独的行中，而不是存储在列中，如下所示：

week    pos    num
   1      1      6
   1      2      8
   1      3     32

那么，您的查询将是一个简单的聚合查询：

select num, count(*) no_picks from dad group by num order by no_picks desc

对于给定的表结构，需要将列撤消到行。在MySQL中，您可以为此使用union all：

select num, count(*) no_picks
from (
    select `1st` num from dad
    union all select `2nd` from dad
    union all select `3rd` from dad
    union all select `4th` from dad
    union all select `5th` from dad
    union all select `6th` from dad
) t
group by num 
order by no_picks