原始表格如下
operation processId errcode
trade 1 200
trade 2 102
trade 3 200
trade 4 200
trade 5 107
trade 6 107
trade 7 200
trade 8 101
trade 9 102
trade 10 200
trade 11 107
bind 17 200
trade 12 200
bind 21 200
validate 4 200
validate 7 201
validate 10 201
validate 17 209
validate 21 207
validate 12 202
passset 1 307
passset 3 308我想得到如下结果
operation errcode num
trade 102 2
trade 107 3
trade 200 6
pass 200 1
pass 201 2
pass 202 1
pass 307 1
pass 308 2这个 validate 以及 passset 将两者视为相同的操作名称 pass ，他们的 processId 我想一定和贸易一样 join 是必须的。我可以用 union 加入几次就得到了结果。
我能用优雅的方式得到结果吗？
1条答案
按热度按时间c90pui9n1#
使用
CASE以及GROUP BY```with data as
(
select case when operation = 'validate' or operation = 'passset'
then 'pass'
else operation end operation,
errcode
from your_table
)
select operation, errcode, count()
from data
where operation = 'trade'
group by operation, errcode
union all
select d1.operation, d1.errcode, count()
from data d1
join data d2 on d1.processId = d2.processId
where d2.operation = 'trade' and d1.operation = 'pass'
group by d1.operation, d1.errcode