Mysql:使用 DATE_SUB 改进嵌套 UNION 查询

Question

我有以下运行完美的 MySQL 查询。它准确地为我提供了我需要的信息。

`SELECT g1 AS Nummer, count(*) AS Aantal
FROM  
  ( SELECT  g1 FROM  getallen
    UNION  ALL 
    SELECT  g2 FROM  getallen
    UNION  ALL 
    SELECT  g3 FROM  getallen
    UNION  ALL 
    SELECT  g4 FROM  getallen
    UNION  ALL 
    SELECT  g5 FROM  getallen
    UNION  ALL 
    SELECT  g6 FROM  getallen
    UNION  ALL 
    SELECT  g7 FROM  getallen
    UNION  ALL 
    SELECT  g8 FROM  getallen
    UNION  ALL 
    SELECT  g9 FROM  getallen
    UNION  ALL 
    SELECT  g10 FROM  getallen
    UNION  ALL 
    SELECT  g11 FROM  getallen
    UNION  ALL 
    SELECT  g12 FROM  getallen
    UNION  ALL 
    SELECT  g13 FROM  getallen
    UNION  ALL 
    SELECT  g14 FROM  getallen
    UNION  ALL 
    SELECT  g15 FROM  getallen
    UNION  ALL 
    SELECT  g16 FROM  getallen
    UNION  ALL 
    SELECT  g17 FROM  getallen
    UNION  ALL 
    SELECT  g18 FROM  getallen
    UNION  ALL 
    SELECT  g19 FROM  getallen
    UNION  ALL 
    SELECT  g20 FROM  getallen
  ) z

GROUP BY  g1
ORDER BY Aantal
DESC
LIMIT 10

我为此只使用了 1 个表，如下所示:
- 日期
- g1 到 g20

但现在我想要一个像这样的 future :

WHERE 'column name' >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

我似乎找不到适合它的方法。有什么想法吗？

Answer 1

只需添加:

where g1 >= date_sub(CURDATE(), interval 1 month)

就在 GROUP BY 子句之前。

您也可以将子查询中的列直接命名为Nummer，这样查询将开始:

SELECT Nummer, count(*) AS Aantal
FROM  (SELECT  g1 as Nummer FROM  getallen
       UNION  ALL 
       SELECT  g2 FROM  getallen
       UNION  ALL 
       . . . 
      ) g
WHERE Nummer >= date_sub(CURDATE(), interval 1 month)
GROUP BY Nummer
ORDER BY Aantal DESC
LIMIT 10;