时间戳

首页 > 解决方案 > 多订单应该怎么做？

sql - 多订单应该怎么做？

问题描述

我想按chart_num 和DATE 排序。但是，对齐时会打印出以下结果：

在我的代码中：

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

在此处输入图像描述

我想要的结果是NULL值回到最新的DATE，如果有chart_num，我想按照chart_num和DATE的顺序排序。

标签： sqlmariadb

解决方案

不要在内部联合所有查询中按结果排序。按chart_num 和日期代替结果排序。

所以代替

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

希望能帮助到你。！

推荐阅读