我有一个关于SQL的问题。
这就是查询。
SELECT Customer,
SUM(IF(date_format(OutputDate, '%Y') = 2020 AND date_format(OutputDate, '%m') = 3, SupplyPrice, 0)) AS March
FROM (SELECT Customer, OutputDate, SupplyPrice FROM `TableOutput`) AS C
GROUP BY Customer
ORDER BY March DESC
LIMIT 20;
结果如下:
Customer March
A 100
B 200
C 300
D 400
...
但我想让客户A和B加在一起作为A(而不是B(或新名称(例如Z(
像这样:
Customer March
A(or Z) 300
C 300
D 400
...
我想修改查询并输出它。
我知道您希望将客户A
和B
组合在一起。您可以使用CASE
表达式:
SELECT
(CASE WHEN customer IN ('A', 'B') THEN 'A' ELSE Customer
END) as real_customer,
SUM(CASE WHEN outputDate >= '2020-03-01' AND outputDate < '2020-04-01' THEN SupplyPrice ELSE 0 END) AS March
FROM `TableOutput`
GROUP BY real_customer
ORDER BY March DESC
LIMIT 20;
注意,我修改了outputDate
上的日期谓词,因此它使用了与日期乱丢词的比较,而不是使用日期函数;这是一种更有效的方法,可以利用可能存在的索引。