sql - 排序 COUNT(CASE WHEN) 结果

Question

我正在使用状态数据库并将状态创建为列，以便计算每个状态中存在的网络记录数。我很想根据 Partnered 列 DESC 对结果进行排序，但我不知道如何或在何处进行排序？？

这是我的代码:

SELECT type,
    COUNT(CASE WHEN status = "NOT_SUBMITTED" THEN storenumber END) AS Not_Submitted,
    COUNT(CASE WHEN status = "PARTNERED" THEN storenumber END) AS Partnered,
    COUNT(CASE WHEN status = "PENDING" THEN storenumber END) AS Pending,
    COUNT(CASE WHEN status = "SUSPENDED" THEN storenumber END) AS Suspended,
    COUNT(CASE WHEN status = "REJECTED" THEN storenumber END) AS Rejected,
FROM Programs
GROUP BY 1;

这是我目前的结果。

<表类="s-表"><头>行类型未提交合作待定暂停拒绝<正文>1abc264733603742定义2481394379736310743你好0196501501024jkl12311851000

Answer 1

您只需添加 ORDER BY Partnered DESC 如下例所示

SELECT type,
    COUNT(CASE WHEN status = "NOT_SUBMITTED" THEN storenumber END) AS Not_Submitted,
    COUNT(CASE WHEN status = "PARTNERED" THEN storenumber END) AS Partnered,
    COUNT(CASE WHEN status = "PENDING" THEN storenumber END) AS Pending,
    COUNT(CASE WHEN status = "SUSPENDED" THEN storenumber END) AS Suspended,
    COUNT(CASE WHEN status = "REJECTED" THEN storenumber END) AS Rejected,
FROM Programs
GROUP BY 1
ORDER BY Partnered DESC          

同时，还要考虑以下选项

SELECT type,
    COUNTIF(status = "NOT_SUBMITTED") AS Not_Submitted,
    COUNTIF(status = "PARTNERED") AS Partnered,
    COUNTIF(status = "PENDING") AS Pending,
    COUNTIF(status = "SUSPENDED") AS Suspended,
    COUNTIF(status = "REJECTED") AS Rejected,
FROM Programs
GROUP BY 1
ORDER BY Partnered DESC         

最后 - 尝试下面的一个(这是我的首选)

SELECT *
FROM (SELECT type, storenumber, status FROM Programs)
PIVOT (
    COUNT(DISTINCT storenumber) 
    FOR status IN ("NOT_SUBMITTED", "PARTNERED", "PENDING", "SUSPENDED", "REJECTED")
)
ORDER BY PARTNERED DESC