如何根据条件对列名进行排序？

Question

我有一张桌子上有以下几栏-

• 事故ID (varchar)
• 创建日期(varchar-例如-‘01-2014年10月’)
• 状态(varchar-例如-“打开”，“关闭”)

我需要根据从创建日期开始的状态和日期范围来统计事件。

例如：

Date Range       || Open          || Closed ||
1-2 days         ||         5     ||       6 ||
2-5 days         ||         4     ||       8 ||
5-15 days        ||         10    ||       15 ||

任何帮助都将不胜感激，谢谢。

Answer 1

SELECT CASE WHEN DATEDIFF(created_date, now()) BETWEEN 1 AND 2
                THEN '1-2 days'
            WHEN DATEDIFF(created_date, now()) BETWEEN 3 AND 5
                THEN '3-5 days'
            WHEN DATEDIFF(created_date, now()) BETWEEN 6 AND 15
                THEN '6-15 days'
            ELSE '> 15 days'
        END AS DateRange,
        SUM(CASE WHEN Status = 'open' THEN 1 ELSE 0 END) AS Open,
        SUM(CASE WHEN Status = 'closed' THEN 1 ELSE 0 END) AS Closed
FROM YourTable
GROUP BY DateRange