As title, if there is a table in the mysql database table, field respectively. A, b, c, d, think according to field a group, and then display the first line of each group, the SQL statement format for: select a, MIN (b), c, d from table group by a,
The SQL query result set, b fields can take to the minimum, then c and d field is take?


the data contentA b c d
1 1 1 1
1 2 2 2
3 3 3 1

Above data content directly from the mysql database query, result sets: 1,1,1,1, but from the inside of the code query, return a result set is: 1,1,3,3,

Ask god to reassure!!!!!!

CodePudding user response:

Select a, MIN (b), MIN (c), MIN (d) from the table group by a

CodePudding user response:

According to the group by the result of a process focus on the first line of the first line is the first line, according to the order of physical storage is unpredictable, random
After the group by data, context switches for each group instead of each line, as a result, the standard SQL, the select list can't use the grouping field, because the violation of the relational model paradigm,
In mysql, extension of standard SQL GROUP BY (in other words, in violation of the relational model paradigm), can use the grouping field in the select field list,
http://www.cnblogs.com/f-ck-need-u/p/8656828.html

CodePudding user response:

reference 1st floor oyljerry response:

select a, MIN (b), MIN (c), MIN (d) from the table group by a

Thank you for your answer, but this is wrong, the other fields is not necessarily the maximum or minimum value, also cannot use other aggregation function

CodePudding user response:

refer to the second floor a905815661 response:

according to the group by the result of a process focus on the first line of the first line is the first line, according to the order of physical storage is unpredictable, random
After the group by data, context switches for each group instead of each line, as a result, the standard SQL, the select list can't use the grouping field, because the violation of the relational model paradigm,
In mysql, extension of standard SQL GROUP BY (in other words, in violation of the relational model paradigm), can use the grouping field in the select field list,
http://www.cnblogs.com/f-ck-need-u/p/8656828.html

Thank you for your answer, according to the meaning of the context, although mysql to group by expanded, but not according to the results of the SQL code to write SQL statements are unpredictable, random, so it is better to according to the SQL standard written statements, is it?

CodePudding user response:

If you want to understand, according to standard SQL natural better (this also said at the same time understand the SQL SERVER, ORACLE SQL syntax of , because they are on the SQL standard strictly abide by the SQL standard),
But since the SQL code for standard, nature can be violated, MySQL, after all, has its own "personality", as long as you don't know to do so on your demand impact (especially to add authorization statement), it doesn't matter ah,

CodePudding user response:

SELECT a, b, c, d FROM the TABLE WHERE (a, CONCAT (b, c, d)) IN (SELECT a, MIN (CONCAT (b, c, d)) BCD FROM TABLE GROUP BY a)