Shulou(Shulou.com)05/31 Report--

This article will explain in detail how MySQL uses union all to obtain union sorting. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

In a project, sometimes for some irreversible reason, the data stored in the table is difficult to meet the display requirements on the page. The previous project has the display function of the article content, the article is divided into three states to be released, has been released, has been offline.

The PROMOTE_STATUS values in which they judge the status in the data table are 0, 1, and 2, respectively. The initial requirement is that the article only shows that it is to be published and has been released, and that the published article is ranked before the published article, and that the articles are sorted according to their own situation. Such an implementation is relatively simple, as can be achieved by the following order by statement.

Order by PROMOTE_STATUS desc, SEQUENCE_ID desc.

After the result was handed over to the test, the product felt that it could be optimized here, and the presentation of the article should be changed to published, to be released, and offline (yes, it was suddenly asked for, and proudly ranked last). What are we going to do? Changing the table to change the published, unreleased, and offline PROMOTE_STATUS corresponding values to 2, 1, 0 is definitely not feasible, because other colleagues have also used this table. If you change the correspondence here. The judgment logic of other colleagues' code has to be changed.

So you think of union all, and then you need to implement the order in which the articles are presented in each of the three states. Therefore, the final idea is to find out the data when the PROMOTE_STATUS is 1, 0, 2 respectively, and then sort the order by according to the situation in each state, and finally return each subset union all to the page to show.

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 allselect 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 allselect 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) this is the end of the article on "how MySQL uses union all to obtain Union ordering". Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.