php - 如何在 MYSQL 中获取汇总计数

Question

如何计算连续数字的个数。

例子

数据库

num1 num2 num3 num4
 10   20   30   40
 40   50   60   70
 20   10   90   80
 01   60   81   99

所以我希望结果适用于整个表:

01 1
10 2
20 2
30 1

等等，如果我只想要前两行的摘要，我会得到:

10 1
20 1
30 1
40 2
50 1
60 1
70 1

Answer 1

要获取整个表的结果，您可以使用如下查询:

SELECT num, COUNT(*) cnt
FROM (
  SELECT num1 AS num FROM tableName
  UNION ALL 
  SELECT num2 AS num FROM tableName
  UNION ALL 
  SELECT num3 AS num FROM tableName
  UNION ALL 
  SELECT num4 AS num FROM tableName
) s
GROUP BY num

要仅获取前两行的结果，您可以使用 LIMIT:

SELECT num, COUNT(*) cnt
FROM (
  SELECT id, num1 AS num FROM tableName
  UNION ALL 
  SELECT id, num2 AS num FROM tableName
  UNION ALL 
  SELECT id, num3 AS num FROM tableName
  UNION ALL 
  SELECT id, num4 AS num FROM tableName
  ORDER BY id
  LIMIT 8
) s
GROUP BY num

其中 8 是 4 列 * 你想要的行数 2，但你需要使用 ORDER BY 子句。一个例子是 here .