场景:status有5个状态:1:未办理,2:开票中,3:开票完成,4:开票请求驳回, 5:已邮寄, 5:已自提
根据不同状态统计某个用户的订单数。统计所有状态都有为抢票数,统计status=3为开票数,统计status=4为驳回数。查询某个用户对应的开票数,抢单数,驳回数。
SQL:
SELECT
COUNT(order_no) as qiangdan,
SUM(CASE WHEN order_status = '3' or order_status = '5' THEN 1 ELSE 0 END) as jaipiao,
SUM(CASE WHEN order_status = '4' THEN 1 ELSE 0 END) as reject
FROM tb_tax_order
WHERE station_staff_id = '6'
查询结果:
注意:函数不能嵌套函数使用,比如不能 SUM(CASE WHEN order_status = '4' THEN COUNT(order_no) ELSE 0 END),这是错误的。