MySQL UNION 2 集来自 1 个表的结果

Question

我有下表

CREATE TABLE IF NOT EXISTS `eurojackpot` (
`datum_trekking` date NOT NULL,
`g1` tinyint(2) NOT NULL,
`g2` tinyint(2) NOT NULL,
`g3` tinyint(2) NOT NULL,
`g4` tinyint(2) NOT NULL,
`g5` tinyint(2) NOT NULL,
`b1` tinyint(2) NOT NULL,
`b2` tinyint(2) NOT NULL,
UNIQUE KEY `datum_trekking` (`datum_trekking`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我执行下面的查询，它部分给出了我正在寻找的结果集。

(SELECT g1 AS Nummer, count(*) AS Aantal
FROM  
( 
SELECT  g1 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  g2 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  g3 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  g4 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  g5 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
) y
GROUP BY  g1
ORDER BY Aantal
DESC
LIMIT 5
)
UNION
(SELECT b1 AS Bonus, count(*) AS aantal_b
FROM  
( 
SELECT  b1 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  b2 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
) z
GROUP BY  b1
ORDER BY aantal_b
DESC
LIMIT 5)

上述查询输出以下数据集:

+-------+--------+
+Nummer + Aantal +
+-------+--------+
+ 3     + 2      +
+ 22    + 2      +
+ 25    + 2      +
+ 41    + 2      +
+ 35    + 2      +
+ 4     + 3      +
+ 2     + 2      +
+ 8     + 2      +
+ 5     + 2      +
+ 3     + 1      +
+-------+--------+

我要找的是一个输出如下的表:

+-------+--------+-------+----------+
+Nummer + Aantal + Bonus + aantal_b +
+-------+--------+-------+----------+
+ 3     + 2      + 4     + 3        +
+ 22    + 2      + 2     + 2        +
+ 25    + 2      + 8     + 2        +
+ 41    + 2      + 5     + 2        +
+ 35    + 2      + 3     + 1        +
+-------+--------+-------+----------+

查询基本上做的是计算数字的出现次数并将其限制为出现次数最多的 5 个数字。它应该计算列 g1 到 g5 和 b1 到 b2 的数字。非常感谢任何提示和提示。我想我忽略了一些东西，但找不到让这个查询正常工作的最后一点

Answer 1

执行 Join 而不是 UNION:

SELECT yy.Nummer, yy.Aantal, zz.Bonus, zz.aantal_b
FROM
(
SELECT 
    g1 AS Nummer, 
    count(*) AS Aantal
FROM  ( 
    SELECT  g1 FROM  eurojackpot
        WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
    SELECT  g2 FROM  eurojackpot
        WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
    SELECT  g3 FROM  eurojackpot
        WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
    SELECT  g4 FROM  eurojackpot
        WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
    SELECT  g5 FROM  eurojackpot
        WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
) y
GROUP BY  g1
ORDER BY Aantal
DESC
LIMIT 5
) yy
JOIN
(SELECT b1 AS Bonus, count(*) AS aantal_b
FROM  
( 
SELECT  b1 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
    UNION  ALL 
SELECT  b2 FROM  eurojackpot
  WHERE datum_trekking >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
) z
GROUP BY  b1
ORDER BY aantal_b
DESC
LIMIT 5) zz
ON y.g1 = z.b1