select联合:mysql-select-month,previous-month-1,month-3

2cmtqfgy  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(420)

根据@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的矩阵,像这样…期望的输出
我尝试使用下面的查询来解决它:
为了解决我的问题,我遇到了这个讨论mysql查询来计算前一个月和这个:mysql:selectpreviousmonth和monthbefore

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)

... 它给人一种感觉

值(位:-(…)仅在对角线上)。
我想要的是这个

.

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

p1tboqfb

p1tboqfb1#

几乎可以肯定的是,有更好的方法来做你想做的事情,但考虑到你所拥有的,你可以这样做:

select max(a), max(b), max(c) from(
  select '1' as a, ''as b, '' as c
  union
  select '' as a, '2' as b, '' as c
  union 
  select '' as a, '' as b, '3' as c
) d;
gwbalxhn

gwbalxhn2#

可以使用条件聚合。只计算满足条件的行。用一个 CASE 如果满足条件,则返回任何非null值。否则 CASE 将按默认值返回null和as count(ex) 不算一行如果 ex 计算结果为 NULL ,行不计算在内。
你可能还想在某种程度上修改你的条件 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;

相关问题