如何将MySql联合查询的结果从2列拆分为4
我正在执行此mysql查询
select merchant_id, count(*) from OrderDetails where OrderDetails.delivery_date between '2019-07-24 00:00:00' and '2019-11-25 23:59:59' group by merchant_id
union all
select merchant_id, count(order_status_id) from OrderDetails where OrderDetails.delivery_date BETWEEN '2019-07-24 00:00:00' and '2019-11-25 23:59:59' group by merchant_id, order_status_id having order_status_id = 10
union
select merchant_id, count(order_status_id) from OrderDetails where OrderDetails.delivery_date between '2019-07-24 00:00:00' and '2019-11-25 23:59:59' group by merchant_id, order_status_id having order_status_id = 11; 但它在两列中返回所有结果，我希望它在4中有，如id，count，return，请提供任何建议
问题来源于stack overflow
写回答
-
您需要sum()而且count()可以忽略自己的union。
select merchant_id , count(1) , sum(case when order_status_id = 10 then 1 else 0 end) as returned , sum(case when order_status_id = 11 then 1 else 0 end) as delivered from OrderDetails where delivery_date between '2019-07-24 00:00:00' and '2019-11-25 23:59:59' group by merchant_id
2019-11-15 13:52:33赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关文章
相关电子书
更多
