应该如何执行多个order by？

kuhbmx9i 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(568)

我想按图表编号和日期排序。但是，对齐后将打印以下结果：
在我的代码中：

SELECT * 
FROM (
    SELECT id, chart_num, chart_name, MIN(DATE) AS DATE, amount, (COUNT(*) = 2) AS result, card_check
    FROM (
        (
            SELECT id, hpd.chart_num AS chart_num, hpd.chart_name AS chart_name, hpd.visit AS DATE, card_amount_received AS amount, card_check_modify AS card_check
                ,row_number() over (PARTITION BY card_amount_received ORDER BY id) AS seqnum
            FROM hospital_payment_data hpd
            WHERE store_mbrno = '135790' AND card_amount_received > 0
        ) 
        UNION ALL (
            SELECT id, ncd. chart_num AS chart_num, ncd. chart_name AS chart_name, DATE_FORMAT(ncd.tranDate,'%Y-%m-%d') AS DATA, amount, card_check_result AS card_check
                ,row_number() over (PARTITION BY amount ORDER BY id) AS seqnum
            FROM noti_card_data ncd
            WHERE (mbrNo = '135790' OR mbrNo = '135791') AND cmd ='승인'
        )
    ) X
    GROUP BY amount, seqnum 
    ORDER BY result DESC
) a 
ORDER BY a.DATE DESC

我想要的结果是空值返回到最新的日期，如果有图表编号，我想按图表编号和日期的顺序排序。

sql mariadb

来源：https://stackoverflow.com/questions/62526680/what-should-be-done-to-do-multiple-order-by

2条答案

按热度按时间

ctzwtxfj1#

感觉我在这个问题上遗漏了一些东西，但是你可以在 ORDER BY 用逗号。从您的文本中不清楚您是希望将日期分组在同一个图表中，还是希望将图表分组在同一个日期中，但是如果我猜错了，您可以交换它。
此外，还有 ORDER BY result DESC 完全是多余的。它不会增加任何结果，通过删除它，我们可以消除整个嵌套级别。

SELECT id, chart_num, chart_name, MIN(DATE) AS DATE, amount, (COUNT(*) = 2) AS result, card_check
FROM (
    (
        SELECT id, hpd.chart_num AS chart_num, hpd.chart_name AS chart_name, hpd.visit AS DATE, card_amount_received AS amount, card_check_modify AS card_check
            ,row_number() over (PARTITION BY card_amount_received ORDER BY id) AS seqnum
        FROM hospital_payment_data hpd
        WHERE store_mbrno = '135790' AND card_amount_received > 0
    ) 
    UNION ALL (
        SELECT id, ncd.chart_num, ncd.chart_name, DATE_FORMAT(ncd.tranDate,'%Y-%m-%d'), amount, card_check_result
            ,row_number() over (PARTITION BY amount ORDER BY id) AS seqnum
        FROM noti_card_data ncd
        WHERE mbrNo IN ('135790', '135791') AND cmd ='승인'
    )
) X
GROUP BY amount, seqnum 
ORDER BY MIN(DATE), coalesce(chart_num,-1), result DESC

赞(0）回复(0）举报 2021-07-24

n9vozmp42#

不要在内部union all查询中按结果排序。按图表编号和日期代替结果排序。
所以代替了

Order by result desc

使用此选项：

Order by chart_num desc, DATE desc

或者，
在外部主查询中：
代替

Order by a.DATE DESC

使用

Order by a.chart_num desc, a.DATE desc

希望有帮助。！

赞(0）回复(0）举报 2021-07-24

我来回答

应该如何执行多个order by？

2条答案

相关问题

热门标签

最新问答