mysql - 使用MySQL CASE求SUM上个月和本月总计

Question

我有一个查询要从 2 个表中生成类(class)名称和总计的列表。它工作正常，但现在我想在同一个查询中获得本月和上个月的总计，所以我添加了几个案例。问题是案例返回空值。

这是我的查询:

$Sql = "SELECT t1.coursename, t1.id, SUM(t2.amount) AS total,

CASE 
WHEN YEAR(t2.date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) AND MONTH(t2.date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) THEN SUM(t2.amount)
ELSE 0 END 
AS lastMonthTotal,      

CASE
WHEN YEAR(t2.date) = YEAR(CURRENT_DATE) AND MONTH(t2.date) = MONTH(CURRENT_DATE) THEN SUM(t2.amount) 
ELSE 0 END 
AS thisMonthTotal

FROM t1
LEFT JOIN t2 ON t2.courseid = t1.courseid
WHERE $pdoCoursesString GROUP BY t1.courseid";

Answer 1

我认为您应该将 CASE 语句放在 SUM 函数中。

SQL fiddle :http://sqlfiddle.com/#!9/4e10e2/5

SQL:

测试数据:

create table t1(courseid int, coursename varchar(10));
create table t2(courseid int, date date, amount int);
insert into t1 values(1,'courseA');
insert into t1 values(2,'courseB');
insert into t2 values(1,'20180211',12);
insert into t2 values(1,'20180111',16);
insert into t2 values(2,'20180101',1);
insert into t2 values(2,'20180201',1);
insert into t2 values(2,'20180101',1);    

查询:

SELECT t1.coursename, t1.courseid, SUM(t2.amount) AS total,
SUM(
  CASE 
  WHEN YEAR(t2.date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH) 
  AND MONTH(t2.date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) 
  THEN 
    t2.amount
  ELSE 
    0 
  END
) AS lastmonthtotal,
SUM(
  CASE 
  WHEN YEAR(t2.date) = YEAR(CURRENT_DATE) 
  AND MONTH(t2.date) = MONTH(CURRENT_DATE) 
  THEN 
    t2.amount
  ELSE 
    0 
  END
) AS thismonthtotal
FROM t1
LEFT JOIN t2 ON t2.courseid = t1.courseid
GROUP BY t1.courseid
;

结果:

| coursename | courseid | total | lastmonthtotal | thismonthtotal |
|------------|----------|-------|----------------|----------------|
|    courseA |        1 |    28 |             16 |             12 |
|    courseB |        2 |     3 |              2 |              1 |