How to use union all in MySQL to get the union sort

Sometimes in a project, due to some irreversible reasons, the data stored in the table is difficult to meet the display requirements on the page. The previous project had a function to display article content. Articles were divided into three states: pending, published, and offline.

The values ​​of the field (PROMOTE_STATUS) used to determine the status in the data table are 0, 1, and 2 respectively. The initial requirement was that articles only be displayed as pending and published, with published being listed before pending, and the two states being sorted according to their own circumstances. This implementation is relatively simple and can be achieved with the following order by statement.

order by PROMOTE_STATUS desc, SEQUENCE_ID desc......

After the test, the product felt that this could be optimized, and the display of articles should be changed to published, to be published, and offline (yes, offline was suddenly required, and it was placed at the end very proudly). What should I do then? It is definitely not feasible to change the corresponding values ​​of PROMOTE_STATUS of released, pending release, and offline to 2, 1, and 0, because other colleagues also use this table. If the correspondence here is changed. The judgment logic of other colleagues' codes must be changed.

So I thought of union all, and then I also needed to implement the display order of the articles in the three states. Therefore, the final idea is to find out the data when PROMOTE_STATUS is 1, 0, and 2 respectively, and then sort by order according to the situation in each state, and finally return each subset to the page for display after union all.

The final SQL statement is as follows:

select
    PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
    PROMOTE_TITLE,
    RELEASE_DATE
    FROM (
      (SELECT
        PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
        PROMOTE_TITLE,
        RELEASE_DATE 
       FROM SYS_TEXT_PROMOTE
       WHERE
         ENABLED_FLAG = '1'
         AND PROMOTE_STATUS=1
         AND SORT_ID = #{params.sortId}
         order by SEQUENCE_ID DESC,LAST_UPDATE_DATE DESC) a)
union all
select
    PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
    PROMOTE_TITLE,
    RELEASE_DATE
    FROM (
      (SELECT
        PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
        PROMOTE_TITLE,
        RELEASE_DATE 
       FROM SYS_TEXT_PROMOTE
       WHERE
        ENABLED_FLAG = '1'
        AND PROMOTE_STATUS=2
        AND SORT_ID = #{params.sortId}
        order by RELEASE_DATE DESC,LAST_UPDATE_DATE DESC) b)
union all
select
    PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
    PROMOTE_TITLE,
    RELEASE_DATE
    FROM (
      (SELECT
        PROMOTE_ID,
    SEQUENCE_ID,
    PROMOTE_STATUS,
        PROMOTE_TITLE,
        RELEASE_DATE 
       FROM SYS_TEXT_PROMOTE
        WHERE
        ENABLED_FLAG = '1'
        AND PROMOTE_STATUS=0
        AND SORT_ID = #{params.sortId}
        order by RELEASE_DATE DESC,LAST_UPDATE_DATE DESC) c)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.