Use of mysql based query statement

1 > Query the data in the data table except for the first three items.

The first thing that came to my mind was this statement

SELECT * FROM admin WHERE userid NOT IN (SELECT userid FROM admin ORDER BY userid LIMIT 3) ORDER BY userid DESC

But running will quote This version of MySQL doesn 't yet support' LIMIT & IN/ALL/ANY/SOME subquery

This means that there is no support for limit when it comes to subqueries, and the other thing that I'm going to get is that using not in for queries is very inefficient

The final solution is

CREATE VIEW view_top3_admin AS SELECT * FROM admin ORDER BY userid LIMIT 3;

Create a view and put the subquery criteria inside the view

Then use this statement again

SELECT * FROM admin a WHERE NOT EXISTS (SELECT 1 FROM view_top3_admin b WHERE b.userid=a.userid ) ORDER BY a.userid DESC

SELECT 1 FROM view_top3_admin b b WHERE b. userid= a. userid means no data is read as long as there is data in the query table

This is to improve the performance of the query, of course, you can replace the inside 1 with null performance is 1. The whole statement means that the admin table value is queried, and the condition is judged to be that the value is not in the subquery table.

2 > The use of union and union all

First, let's explain that these two keywords provide the UNION and UNION ALL keywords in the mysql database. Both of these keywords combine the results into one, but they are different in terms of usage and efficiency

After table linking, UNION will filter out duplicate records, so it will sort the result set generated after table linking, delete duplicate records and return the result.

select * from table union select * from tabl

UNION ALL simply merges the two results and returns if there is duplicate data in the two result sets returned, then the returned result set will contain duplicate data

select * from table union all select * from tabl

In terms of efficiency, UNION ALL is much faster than UNION, so use UNION if you can be sure that the two merged result sets do not contain duplicate data

These two keywords are used more often in reports