如何将union与具有order by的两个语句结合使用

zfycwa2u 于 2021-06-20 发布在 Mysql

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

我试图从不同的表中获取文件夹和文件名，并根据每个查询语句的结果，按它们的降序排序 update_date 而不是使用 UNION .
如果我分别运行这两个查询，它会工作，结果将按照日期的降序从数据库中检索。但当我使用 UNION 对他们来说，这并不能给我正确的答案。
正确的方法是什么？

(
    SELECT create_date, folder_name AS actual_name, folder_id, folder_displayed_name AS displayed_name, update_date, delete_flag, NULL AS img_url, NULL AS protect_flag, NULL AS file_type, list_order
    FROM aaaaa_estate.docs_folder
    WHERE delete_flag =0
    AND inside_of_folder =  ''
    ORDER BY update_date DESC

    )
    UNION (

    SELECT create_date, file_name AS actual_name, id, displayed_name, update_date, delete_flag, img_url, protect_flag, file_type, list_order
    FROM aaaaa_estate.docs_contract
    WHERE delete_flag =0
    AND inside_of =  ''
    ORDER BY update_date DESC
)

如果我在最后使用order by子句。它根据数据的大小对所有数据进行排序
update_date ORDER BY DESC 我从来都不想要。
我想向用户显示文档列表，就像google在googledrive中显示的那样，总是先显示文件夹，然后显示文件。他们每个人都按 update_date DESC

sql mysql union sql-order-by

来源：https://stackoverflow.com/questions/52854823/how-to-use-union-with-two-statements-having-order-by

2条答案

按热度按时间

nhjlsmyf1#

在子查询外使用order by

select t.* from 
(
    SELECT 1 as type, create_date, folder_name AS actual_name, folder_id, folder_displayed_name AS displayed_name, update_date, delete_flag, NULL AS img_url, NULL AS protect_flag, NULL AS file_type, list_order
    FROM aaaaa_estate.docs_folder
    WHERE delete_flag =0
    AND inside_of_folder =  ''   

    UNION     
    SELECT 2 ,create_date, file_name AS actual_name, id, displayed_name, update_date, delete_flag, img_url, protect_flag, file_type, list_order
    FROM aaaaa_estate.docs_contract
    WHERE delete_flag =0
    AND inside_of =  ''

) t order by type asc, update_date desc

正如您所提到的，您总是先文件夹，然后文件，所以将其分为两种类型以保持正确的顺序

赞(0）回复(0）举报 2021-06-20

3qpi33ja2#

你可以试试下面的方法 ORDER BY 子句取决于两列：

SELECT * FROM
(
    SELECT 1 qry_nr, create_date, folder_name AS actual_name, folder_id, folder_displayed_name AS displayed_name, update_date, delete_flag, NULL AS img_url, NULL AS protect_flag, NULL AS file_type, list_order
    FROM aaaaa_estate.docs_folder
    WHERE delete_flag =0
    AND inside_of_folder =  ''

    UNION 

    SELECT 2 qry_nr, create_date, file_name AS actual_name, id, displayed_name, update_date, delete_flag, img_url, protect_flag, file_type, list_order
    FROM aaaaa_estate.docs_contract
    WHERE delete_flag =0
    AND inside_of =  ''
 -- ORDER BY update_date DESC
) q
ORDER BY q.actual_name, q.qry_nr;

赞(0）回复(0）举报 2021-06-20

我来回答

如何将union与具有order by的两个语句结合使用

2条答案

相关问题

热门标签

最新问答