如何在mysql中的union子句中对结果进行排序

艾哈迈德

我想获取所有记录，直到它获得 total 的值，但我想按日期排序 DESC 就像在这个查询中一样：

SELECT NULL AS donation_acc_name, NULL AS dopu_purpose, NULL AS dopu_second_purpose, NULL AS dopu_ref_id, NULL AS total FROM dual WHERE (@total := 0) 

UNION 
(SELECT donations.donation_acc_name, dopu_purpose, dopu_second_purpose, dopu_ref_id, @total := @total + dopu_remain_value AS total 
       FROM donation_purposes 
       LEFT JOIN donations ON donation_purposes.dopu_donation_id = donations.donation_id 
       WHERE @total < 5000 AND dopu_id IN (100,136) AND dopu_remain_value > 0 AND dopu_finished = 0 
    ORDER BY donation_purposes.dopu_date DESC)

但 order by 不起作用！！

你有什么解决办法吗？提前致谢：）

去世

您必须使用别名为第一个查询定义一个附加列（作为 NULL），并将dopu_dateORDER BY 子句应用于UNION第二个查询的结果，而不仅仅是第二个查询：

SELECT donation_acc_name, dopu_purpose, dopu_second_purpose, dopu_ref_id, total
FROM (
    SELECT NULL AS dopu_date, NULL AS donation_acc_name, NULL AS dopu_purpose, NULL AS dopu_second_purpose, NULL AS dopu_ref_id, NULL AS total FROM dual WHERE (@total := 0) 
    UNION 
    SELECT donation_purposes.dopu_date, donations.donation_acc_name, dopu_purpose, dopu_second_purpose, dopu_ref_id, @total := @total + dopu_remain_value AS total 
    FROM donation_purposes 
    LEFT JOIN donations ON donation_purposes.dopu_donation_id = donations.donation_id 
    WHERE @total < 5000 AND dopu_id IN (100,136) AND dopu_remain_value > 0 AND dopu_finished = 0 
) t
ORDER BY dopu_date DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。