mysql - 每列显示每个 date_sub 的值

Question

我想在一个查询中显示商品代码的每月销售额。但我现在唯一能做的就是得到它的全部。我正在使用 date_sub。

select p.itemcode, sa.quantity
from sales sa join product p on p.itemcode = sa.itemcode
where DATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) and sa.quantity < 50
group by p.itemcode

上面显示的查询显示了以下输出:

Itemcode        quantity
694                48
7891               45
B-04.020.12405     48
B-04.020.12407     49

我想显示的输出是:

Itemcode          1   2   3    4   5   6
694               3   10  1    20  1   0 
7891              3   10  1    20  1   0 
B-04.020.12405    0   0   0    0   3   45
B-04.020.12407    3   10  1    20  1   0

谢谢!

Answer 1

以下查询使用 CASE 语句检查不同的年龄并相应地添加数量:

select p.itemcode, 
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) THEN sa.quantity ELSE 0 
)  1,
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AND DATE < DATE_SUB(CURDATE(), INTERVAL 1 MONTH) THEN sa.quantity ELSE 0 
)  2,
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AND DATE < DATE_SUB(CURDATE(), INTERVAL 2 MONTH) THEN sa.quantity ELSE 0 
)  3,
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 4 MONTH) AND DATE < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) THEN sa.quantity ELSE 0 
)  4,
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH) AND DATE < DATE_SUB(CURDATE(), INTERVAL 4 MONTH) THEN sa.quantity ELSE 0 
)  5,
SUM(
    CASE WHEN DATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND DATE < DATE_SUB(CURDATE(), INTERVAL 5 MONTH) THEN sa.quantity ELSE 0 
)  6
from sales sa join product p on p.itemcode = sa.itemcode
where DATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) and sa.quantity < 50
group by p.itemcode;