MySQL根据条件分别查询同一列的计数

Question

我们有一个订单表，其中的字段如下，例如

Timestamp PaymentID OrderID
341231231 6           1
342131231 12          2
123123123 18          3
123123122 14          4
123123143 12          5
433453454 6           6
445456456 18          7

我们想要做的是获得一个输出，该输出将为我们提供有关每种付款类型的订单计数的月度报告，但付款将合并在一起，例如6,8 PaymentID 属于类型 C，因此应将两者的计数加在一起 所有其他 PaymentID 均属于 P 类型

所以我们想要的输出如下。

Year   Month   C_Orders P_Orders
2015   01       0        4
2015   02       4        3
2015   03       1        0
2015   04       2        1

我们尝试了 2 个查询，但输出不正确

  select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, co.payment_id, count(co.payment_id) as c_orders,co1.payment_id, count(co1.payment_id) as p_orders from
orders as co, orders as co1
WHERE  co.payment_id in (6,18)
AND co1.payment_id not in (6,18)
GROUP BY year,month

并且

select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, 'COD', count(co.payment_id) as cod_orders
from
orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2014-01-01 00:00:00','+00:00','+5:30')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2020-12-31 23:59:59','+00:00','+5:30')) AND co.is_parent_order = 'N' AND co.status IN ('C','G','E','P') AND co.payment_id in (6,18)
GROUP BY year,month
union 
select SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),1,4) as year,SUBSTRING(CONVERT_TZ(FROM_UNIXTIME(co.timestamp),'+00:00','+5:30'),6,2) as month, 'PREPAID', count(co.payment_id) as prepaid_orders
from
orders as co
WHERE co.timestamp >= UNIX_TIMESTAMP(CONVERT_TZ('2014-01-01 00:00:00','+00:00','+5:30')) AND co.timestamp <= UNIX_TIMESTAMP(CONVERT_TZ('2020-12-31 23:59:59','+00:00','+5:30')) AND co.is_parent_order = 'N' AND co.status IN ('C','G','E','P') AND co.payment_id not in (6,18)
GROUP BY year,month

Answer 1

使用 case 语句仅在满足条件时对值进行求和/计数。

select year, month,
sum(case when payment_id in (6,18) then 1 else 0 end) as 'payment_id in (6,18)',
count(case when payment_id not in (6,18) then payment_id else null end) as 'payment_id not in (6,18)'
from table
group by 1,2