Talking about the _mysql of SQL database to heavy

About the SQL to go heavy, I briefly talk about their own profile, if you have suggestions or do not understand the welcome to point out a lot.

There are two most common ways to go about SQL: Distinct and row_number () and, of course, row_number () There are many other important features in addition to weight, and I'll give you a brief talk about what I actually use in practice.

If there is a UserInfo table, the following figure:

Now we're going to remove the completely duplicate data: SELECT DISTINCT * FROM dbo. UserInfo results are as follows:

　　

But now there is a new demand, to the name of ' John ' to the weight, that is, the same name as long as a data, if you still use distinct words will be very troublesome. You must be smart enough to think that this is the time for Row_number (). All we have to do is execute a word like this. select * FROM (select Row_number ()-PARTITION by Name-ID) as rowid,* from dbo. UserInfo) x WHERE x.rowid=1; it looks a little long, but it's easy to understand. The results now implemented are shown below:

Now the duplicate name of the data only takes one, and is based on the SQL statement in the order by ID of the first, where partition by followed by the go to the heavy field, perhaps you want to ask why to add where x.rowid=1 it, Now, let's not add this condition to see how the result is: SELECT * FROM (select Row_number () over (PARTITION by Name Order by ID) as rowid,* from dbo. UserInfo) x, as shown below:

　　

Now we have seen that if you do not add all the data will be detected, so the rowid=1 data is the data after the heavy;

Row_number () In addition to being able to go heavy and get the row number of the table, now we just need to take a statement: SELECT Row_number () over (the order by ID) as rownum,* from dbo. UserInfo, the results are shown below:

　　

Get all the data, but also get the line number of each data, in which the order of the data can be adjusted according to orders by ID, that is, not to heavy words without partition by name, this feature in the paging use more, if each page 15 data, When paging, directly add conditions rownum Between 1 and 15 on the line. Lone Wolf published a few blogs, usually lazy, hope for everyone useful.