Combined Query in MySQL

To make it easier to see the effect, we first create a news table. The table structure is as follows:

 

 

Now I want to sort this batch of data. The sorting rules are as follows:

1) The highest click rate and title focus on the top of the page.

2) The rest are sorted by Update time.

I. The first thought was to use unoin for joint queries:

Select * from news where title = "Today's focus" order by click DESC

Unoin all

Select * from news order by modified_at DESC

 

Result prompt:

 

Original
To
Use order
By
Or limit
Clause should be placed in parentheses. Change the statement

(Select * from news where title = "Today's focus" order by click DESC)

Unoin all

(Select * from news order by modified_at DESC)

 

Ii. Duplicate data found after Query
Because the second query queries all records, and the first query queries some records.

1) make a limit in the second SELECT query to find all statements whose title is not an SQL statement.

2) or replace unoin all with unoin. Because unoin removes duplicate records in the result set of the Union query.

 

3. You find that the result set is not sorted as you think

 

The sorting result you want is (2, 1, 6, 3, 4, 5, 7, 11, 9, 12, 13, 8)

However, the query result is (1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13)

She seems to have re-ordered the result set by ID because:
Used in parentheses for a single select
Statement order
Only when
Only after integration. Otherwise, order
By
Removed by optimization.

 

(Select * from news where title = "Today's focus" order by click DESC limit 100)

Unoin all

(Select * from news order by modified_at DESC limit 100)

In this way, you can obtain the result set you want.

 

But the problem is that my limit value must be set to a large value. It is certain that, according to the requirements described at the beginning, the limit value should be better than the number of records in the database, otherwise, the queried data is incomplete. This limits the flexibility of the entire SQL statement. However, according to the description in the official documents, this is required if unoin joint query is used. I can't think of other methods for a while.