如何根据日期列将行转换为列? [英] how to convert rows to columns based on date column?

问题描述

这是SQL小提琴

http://sqlfiddle.com/#!9/25542/2/0

我只想获取我的数据

Sale_On    | Count Refunded | Count Sale | Count Cashback
-------------------------------------------------------
2015-09-07 |   1            |    5       |     1

我了解了MySQL中的"case-when"，但是在这种情况下，我必须手动编写每个order_status.

I read about the 'case-when' in MySQL but, in this case I have to write each order_status manually.

我该怎么做?还有其他解决方案，而不是将行转换为列?

How can I do that? Any other solution rather than to convert rows to columns ?

推荐答案

如果您愿意对状态进行硬编码，则可以对它们进行汇总:

If you are happy to hard-code the statuses, you could perform an aggregation over them:

SELECT   DATE(o.sale_on),
         SUM(od.order_status=1) AS Sales,
         SUM(od.order_status=2) AS Refunds,
         SUM(od.order_status=3) AS Cashbacks
FROM     orders o RIGHT JOIN order_detail od USING (order_id)
GROUP BY DATE(o.sale_on)

在 sqlfiddle 上查看.

否则，您实际上需要使用对 master_order_status 表的查询来动态生成类似的SQL，以生成选择列表中的各个列.

Otherwise you'd effectively need to generate similar SQL dynamically using a query on your master_order_status table to generate the respective columns in the select list.

这篇关于如何根据日期列将行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！