mysql - SELECT UNION : MySQL Select month, 上个月 - 1，上个月 - 3

Question

根据 @gordon 的评论进行编辑 ---

假设以下场景

CREATE TABLE card (date_field, numcard, month);

INSERT INTO card (date_filed, numcard, month) 
VALUES
    (2018-06-01, 12531, June-2018),
    (2018-06-02, 29182, June-2018),
    (2018-05-01, 12781, May-2018),
    (2018-05-29, 56171, May-2018),
    (2018-05-10, 27191, May-2108),
    (2018-04-10, 83231, April-2018),
    (2018-03-01, 31131, March-2018),
    (2018-03-02, 47131, March-2018),
    (2018-02-15, 34617, February-2018);

在这种情况下，我尝试使用一个查询来获取 2018 年 6 月 (M)、2018 年 5 月 (M-1) 和 2018 年 3 月 (M-3) 的卡号...以及输出应该是像这样的 1X3 矩阵... Desired Output

我尝试使用下面的查询来解决它:

in the quest to solve my question, I came across this discussion MySQL Query to calculate the Previous Month and this one : MySQL: Select previous month and month before

    SELECT count(*) as 'M', '' as'M-1', '' as 'M-3' FROM my_table WHERE YEAR(date_field) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH) AND MONTH(date_field) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH)
UNION
    SELECT '' as 'M', count(*) as'M-1', '' as 'M-3'  FROM my_table WHERE YEAR(date_field) = YEAR(CURRENT_DATE - INTERVAL 3 MONTH) AND MONTH(date_field) = MONTH(CURRENT_DATE - INTERVAL 3 MONTH)
UNION
    SELECT '' as 'M', '' as 'M-1', count(*) as 'M-3'  FROM my_table WHERE YEAR(date_field) = YEAR(CURRENT_DATE - INTERVAL 4 MONTH) AND MONTH(date_field) = MONTH(CURRENT_DATE - INTERVAL 4 MONTH)

...它给出了

仅对角线上有值(位:-( ...)。

我想要的是这个

.

*** 感谢您的帮助和见解。周末愉快!

Answer 1

您可以使用条件聚合。只计算满足条件的行。使用 CASE 执行此操作，如果满足条件，则返回任何非空值。否则，CASE 将默认返回 null，并且如果 ex 计算结果为 NULL，则 count(ex) 不会计算行。 code>，该行不计算在内。

您可能还想以某种方式重新处理您的条件，即 date_field 是比较运算符一侧的完整表达式，以便可以将 date_field 上的索引用过的。 (对于 WHEN 中的表达式来说并不那么重要，但对于 WHERE 子句中的表达式来说并不那么重要。)

SELECT count(CASE
               WHEN date_field >= current_date - interval 2 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M`,
       count(CASE
               WHEN date_field >= current_date - interval 3 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 2 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M-1`,
       count(CASE
               WHEN date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
                    AND date_field < current_date - interval 3 month - interval dayofmonth(current_date) - 1 day THEN
                 1
             END) `M-3`,
       FROM my_table
       WHERE date_field >= current_date - interval 4 month - interval dayofmonth(current_date) - 1 day
             AND date_field < current_date - interval 1 month - interval dayofmonth(current_date) - 1 day;