Recent business Feedback A query exception problem requires the DBA to interpret the query result exceptions and help them resolve the problem. The problem essence is the problem of sorting the maximum within a group, and I built the test case according to the business requirements
Test Cases
--Build Table Create table testorder (id int not null,no int not null,name Char (TEN) not null,primary key (ID)) engine=innodb;--write Data insert into testorder values ("Mike"), (2,2, ' John '), (3,3, ' Wyett '), (+, ' Herry '), (5,5, ' Mike '), (6,1, ' John '), (7,2, ' John '), (8,1, ' Mike '), (9,1, ' Mike ');--Query 1select * from testorder;+----+----+-------+| id | no | name |+----+----+-------+| 1 | 1 | mike | | 2 | 2 | john | | 3 | 3 | wyett | | 4 | 4 | herry | | 5 | 5 | mike | | 6 | 1 | john | | 7 | 2 | john | | 8 | 1 | mike | | 9 | 1 | mike |+----+----+-------+--query 2select * from testorder order by no desc;+----+----+-------+| id | no | name |+----+----+-------+| 5 | 5 | mike | | 4 | 4 | herry | | 3 | 3 | wyett | | 2 | 2 | john | | 7 | 2 | john | | 1 | 1 | mike | | 6 | 1 | john | | 8 | 1 | mike | | 9 | 1 | mike |+----+----+-------+--query 3select * from (SELECT ID,NO,NAME FROM TESTORDER ORDER BY NO DESC) a group by a.name;
Query 3 This SQL is what we need to discuss and also the SQL used by the line of business to achieve the maximum value for the sort within the group. Standard Programmer feedback Problem mode:xxx point in time before the normal query, which suddenly is not normal, your DBA did what changes? I restored the data to my test machine, and the return value was normal. For the moment , whether the posture is correct, the analysis of this SQL, we can actually see: (1) Programmer The results of group by execution are expected to be evaluated according to the data order of temporary Table A, (2) the programmer does not take into account the factors of the version factor and the change of data volume; To do this, I built the test case above.
Test
In different versions of MySQL to test: found in Percona 5.5,percona 5.1,mysql 5.6 Close sql_mode= only_full_group_by, MySQL5.1 and other versions, the return value is exactly as the programmer expects the order, according to order by no DESC, the same name returns the maximum value of the data;
+----+----+-------+| id | no | name |+----+----+-------+| 4 | 4 | herry | | 2 | 2 | john | | 5 | 5 | mike | | 3 | 3 | wyett |+----+----+-------+
in mysql5.7, close the sql_mode= only_full_group_by and mariadb 10.* versions, the same name value is returned by fetching the first data row written, is returned according to the logical storage order of the data;
+----+----+-------+| ID | No | Name |+----+----+-------+| 4 | 4 | Herry | | 2 | 2 | John | | 1 | 1 | Mike | | 3 | 3 | Wyett |+----+----+-------+
In fact, here, SQL is equivalent to select Id,no,name from Testorder group by name.
Here we see different versions of the return value is different, first put the data amount of changes caused by different results of the discussion, because the size of the data volume is difficult to test.
Official documents
for the above test results, in the official document, like the following reference
If only_full_group_by is disabled ... in this case, the server is free to choose any value From each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you Want. furthermore, the selection of values from each group cannot be influenced by adding an order by clause. result set sorting occurs after values have been chosen, and order by does not affect which value within each group the server chooses.
Only_full_group_by This sql_mode is introduced in mysql5.6 (MARIADB 10.0), but the content discussed in this article is not related to it, you can view the document yourself, there is no discussion here. The official documents in 5.6,5.5 have the same content, and mariadb have similar explanations.
If you select a non-grouped column or a value computed from a non-grouped column, it is undefined which row the returned V Alue is taken from. This isn't permitted if the only_full_group_by Sql_mode is used.
a query such asselect field1, field2 from ( SELECT field1, field2 FROM table1 ORDER BY field2 ) aliasreturns a result set that is not necessarily ordered by field2. this is not a bug. a "Table" (And subquery in the from clause too) is - According to the sql standard - an unordered set of rows. Rows in a table (or in a subquery in the from clause) do not come in any specific order.
Well, with these explanations, the question is clear:
Therefore, the correct return value obtained by the business is also a mistake.
Solutions
So how do you solve this problem?
There are some SQL on the Internet, obviously do not meet the needs, here to do a demonstration, I hope students avoid being misled:
Error SQL Collection
Select Id,sbustring (Group_concat (distinct no ORDER BY no desc separator "),", 1), name from Testorder GROUP by name;
---by adding an index to affect the returned result set order ALTER TABLE Testorder Add index idx_no_name (no desc, name);--the result proves that even so, DESC will not be executed correctly;
--Our Programmer's wording select * FROM (select Id,no,name from Testorder ORDER BY no DESC) a GROUP by a.name
Select Id,max (No), name from Testorder Group by name
We can write this, though not very efficient.
Select A.id,a.no,a.name from Testorder a inner join (select MAX (NO) no,name from Testorder Group by name) B on A.no=b.no and A.name=b.name GROUP by Name,no
Or so
Select A.id,a.no,a.name from Testorder a group by a.name,a.no have a.no= (select Max (no) from Testorder where name=a.name )
This article is from the "Technical corner of Wyett" blog, please be sure to keep this source http://wyett.blog.51cto.com/9244961/1909546
MySQL group within the sorting to take the maximum value