The mysql group obtains the first few records (ranking) in each group and the groupby and orderby research bitsCN.com -- obtains the data of the row where the maximum (small) value is located by grouping by a certain field
/*
The data is as follows:
Name val memo
A 2 a2 (the second value of)
A 1 a1 -- the first value of
A 3 a3: The third value of
B 1 b1 -- the first value of B
B 3 b3: The third value of B
B 2 b2b2b2b2
B 4 b4b4
B 5 b5b5b5b5b5
*/
-- Create a table and insert data:
Create table tb (name varchar (10), val int, memo varchar (20 ))
Insert into tb values ('A', 2, 'A2 (second value of )')
Insert into tb values ('A', 1, 'A1 -- the first value of ')
Insert into tb values ('A', 3, 'A3: The third value of ')
Insert into tb values ('B', 1, 'b1 -- the first value of B ')
Insert into tb values ('B', 3, 'b3: The third value of B ')
Insert into tb values ('B', 2, 'b2b2b2b2 ')
Insert into tb values ('B', 4, 'b4b4 ')
Insert into tb values ('B', 5, 'b5b5b5b5 ')
Go
-- 1. group data by name to get the data of the row with the largest val value.
-- Method 1: select a. * from tb a where val = (select max (val) from tb where name = a. name) order by a. name
-- Method 2:
Select a. * from tb a where not exists (select 1 from tb where name = a. name and val> a. val)
-- Method 3:
Select. * from tb a, (select name, max (val) val from tb group by name) B where. name = B. name and. val = B. val order by. name
-- Method 4:
Select. * from tb a inner join (select name, max (val) val from tb group by name) B on. name = B. name and. val = B. val order by. name
-- Method 5
Select a. * from tb a where 1> (select count (*) from tb where name = a. name and val> a. val) order by a. name
/*
Name val memo
-----------------------------------------
A 3 a3: The third value of
B 5 b5b5b5b5b5
*/
I recommend using 1, 3, 4. The results show that 1, 3, 4 are the same, and 2, 5 are less efficient. However, if I have the same 3, 4 efficiency, there is no doubt that 1 is different.
-- 2. group the data of the row with the smallest val value by name.
-- Method 1: select a. * from tb a where val = (select min (val) from tb where name = a. name) order by a. name
-- Method 2:
Select a. * from tb a where not exists (select 1 from tb where name = a. name and val <a. val)
-- Method 3:
Select. * from tb a, (select name, min (val) val from tb group by name) B where. name = B. name and. val = B. val order by. name
-- Method 4:
Select. * from tb a inner join (select name, min (val) val from tb group by name) B on. name = B. name and. val = B. val order by. name
-- Method 5
Select a. * from tb a where 1> (select count (*) from tb where name = a. name and val <a. val) order by a. name
/*
Name val memo
-----------------------------------------
A 1 a1 -- the first value of
B 1 b1 -- the first value of B
*/
-- 3. group by name to obtain the data of the row that appears for the first time.
Select a. * from tb a where val = (select top 1 val from tb where name = a. name) order by a. name
/*
Name val memo
-----------------------------------------
A 2 a2 (the second value of)
B 1 b1 -- the first value of B
*/
-- 4. a random data entry is grouped by name.
Select a. * from tb a where val = (select top 1 val from tb where name = a. name order by newid () order by a. name /*
Name val memo
-----------------------------------------
A 1 a1 -- the first value of
B 5 b5b5b5b5b5
*/
-- 5. group by name to get the smallest two (N) val values
Select. * from tb a where 2> (select count (*) from tb where name =. name and val <. val) order by. name,. valselect. * from tb a where val in (select top 2 val from tb where name =. name order by val) order by. name,. val
Select. * from tb a where exists (select count (*) from tb where name =. name and val <. val having Count (*) <2) order by. name
/*
Name val memo
-----------------------------------------
A 1 a1 -- the first value of
A 2 a2 (the second value of)
B 1 b1 -- the first value of B
B 2 b2b2b2b2
*/
-- 6. group by name to get the maximum two (N) val values
Select a. * from tb a where 2> (select count (*) from tb where name = a. name and val> a. val) order by a. name, a. val
Select a. * from tb a where val in (select top 2 val from tb where name = a. name order by val desc) order by a. name, a. val
Select. * from tb a where exists (select count (*) from tb where name =. name and val>. val having Count (*) <2) order by. name
/*
Name val memo
-----------------------------------------
A 2 a2 (the second value of)
A 3 a3: The third value of
B 4 b4b4
B 5 b5b5b5b5b5
*/
-- 7. if the data in the entire row is repeated, all columns are the same (for example, the data in rows 5 and 6 in the following table is identical ).
The maximum two (N) val values are grouped by name.
/*
The data is as follows:
Name val memo
A 2 a2 (the second value of)
A 1 a1 -- the first value of
A 1 a1 -- the first value of
A 3 a3: The third value of
A 3 a3: The third value of
B 1 b1 -- the first value of B
B 3 b3: The third value of B
B 2 b2b2b2b2
B 4 b4b4
B 5 b5b5b5b5b5
*/
Appendix: Studies on mysql "group by" and "order"
These two days make it difficult to query a data. The main reason is that the understanding of group by is not deep enough. This is the case.
I think many people have met this requirement. Below is my content table simulation
I need to retrieve the latest content in each category.
Select * from test group by category_id order by 'date'
The result is as follows:
Obviously. This is not the data I want, because the execution sequence of msyql is
Reference
Write order: select... from... where... group by... having... order ..
Execution sequence: from... where... group by... having... select... order...
Therefore, the result obtained by order by is the final result of grouping.
The result from where to from is as follows.
At group by, multiple groups are obtained according to category_id.
When the select statement is adopted, the result is as follows:
Even order by is sorted from the above results. Not the latest information for each category.
Back to my goal-the latest information in the category
According to the above analysis, only the first information in the group is obtained when group by is selected. There are two solutions
1. where + group by (sort groups)
2. the data returned from form has the following hands and feet (I .e., subquery)
Solution by where + group
I only find that group_concat () can be used to sort groups in group by, but group_concat is used to concatenate values in fields in the group.
Select group_concat (id order by 'date' desc) from 'test' group by category_id
Try again
Select * from 'test' where id in (select SUBSTRING_INDEX (group_concat (id order by 'date' desc), ',', 1) from 'test' group by category_id) order by 'date' desc
Subquery solution
Select * from (select * from 'test' order by 'date' desc) 'temp 'group by category_id order by 'date' desc
BitsCN.com
