<mysql>group by 后选择哪条记录

[wisefree]

初学 sql，主要在学 sql 查询，遇到 group by 的问题，请大家指教

• group by 后，选择记录是选择哪一条呢?

select * from table group by <字段 1>

• 如果我先排序得到一个子查询，然后再 group by，选择哪一条呢？

select * from (select * from table order by <字段 2>) group by <字段 1>

• 如果表中存在自增的主键，先排序得到一个子查询，然后再 group by，选择哪一条呢？主键好像影响挺大的

select * from (select * from table order by <字段 2>) group by <字段 1>

---

还有一个问题， mysql，having 可以用 select 定义的字段别名，这个和标准的 sql 不一样。。。 不都是 from -> where -> group by -> having -> select -> order by -> limit

[liprais]

你试过了么?

[wisefree]

@liprais 试过，但是不确定，受到了主键影响。但是在网上搜了很多答案，没有找到满意的解释。

[syncher]

> select * from (select * from table order by <字段 2>) group by <字段 1>

- 首先你的这句 SQL 是有问题的，字句查询有 3 种，where 型、from 型和 exists 型。本例中如果使用 from 子查询必须为查询结果命名，不妨可以这样

select * from (select * from table order by <字段 2>) as tmp_tb group by <字段 1>

- 然后说一说 group by 的问题

group by 是用于分组统计的，会按照分组字段进行排序，如果 order by 和 group by 同时出现，order by 是对 group by 的结果排序，因此取的是根据 group by 排序后各组第一条，但这是有逻辑错误的，好像只有在 MySQL 中可行。

这句 SQL 就好比一个班级的女生按照寝室分组，然后你想知道分组的结果属于谁？ 其实谁都不属于，但你可以得到每个组中属性的最大值，最小值，或者具有分组统计意义的信息。

- 最后关于 group by 使用索引是一个相对较复杂的概念，可参考 http://blog.csdn.net/caomiao2006/article/details/52140993 这篇博文。

[wisefree]

@liprais 抱歉，v 友指正了，我确实忘记写子查询的别名了。

[wisefree]

@syncher 谢谢指出问题，我忘记写子查询的别名了，>_<

"group by 是用于分组统计的，会按照分组字段进行排序，如果 order by 和 group by 同时出现，order by 是对 group by 的结果排序，因此取的是根据 group by 排序后各组第一条，但这是有逻辑错误的，好像只有在 MySQL 中可行。"

select * from (select * from table order by <字段 2>) as tmp_tb group by <字段 1>

我的理解是：
select * from table order by <字段 2> 得到了一个有顺序的表

然后 group by <字段 1> ，得到的时每个分组第一个，又因为之前已经排好序了，所以取的是<字段 2>升序排列的第一个。

但是在实际操作中，如果有自增主键的话，一般取到了主键小的那一条数据

[msg7086]

https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. 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 values within each group the server chooses.

MySQL 会随机取而且不保证每次结果相同。

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
从 MySQL 5.7.5 起，SELECT 不在 GROUP BY 中的字段将会导致数据库拒绝执行查询。

另外 SQL 92/99 标准里是禁止这种做法的，SELECT * FROM X GROUP BY X 是非法 SQL 语句。

[liprais]

"select * from table group by <字段 1>"
随机选择一条
"select * from (select * from table order by <字段 2>) group by <字段 1>"
随机选择一条，而且子查询里面的 order by 会被优化掉
"select * from (select * from table order by <字段 2>) group by <字段 1>"
跟是不是主键没关系，仍然是随机选择一条

order by 只有后面跟着 limit 在子查询才有意义

"还有一个问题，mysql，having 可以用 select 定义的字段别名，这个和标准的 sql 不一样。。。 不都是 from -> where -> group by -> having -> select -> order by -> limit"

只要在 parse 的时候记住位置就行了，好多数据库都可以这样,teradata,hive, for example.

[liprais]

另外 mysq 对标准支持的特别差，要是只是学 sql 的话 postgresql 会是比较好的选择

[binjjam]

@msg7086
"从 MySQL 5.7.5 起，SELECT 不在 GROUP BY 中的字段将会导致数据库拒绝执行查询。 "
不是禁止吧？只是默认的 sql_mode 不允许，还是可以通过更改 sql_mode 来执行这种智障操作的

[wisefree]

@msg7086 十分感谢！

[wisefree]

@liprais “随机选择一条，而且子查询里面的 order by 会被优化掉”，这个属于 sql 高级内容了吧，>_<.

select * from (select * from table order by <字段 2>) as temp group by <字段 1>

这种写法在网上的教程中，特别多，确实想不到是随机的，那我以后还是尽量用 min max 这些聚合函数来处理吧。

select * from (select * from table order by <字段 2> limit 1000) as temp group by <字段 1>
在子查询中加入 limit，确实有了效果，厉害啊！


---------
公司在用 mysql，所以主要在学 mysql,~_~

[syncher]

@wisefree 哈哈，楼上各位是正解，给你误导了。

[qinxi]

@binjjam 正解.

[wisefree]

@syncher 真心感谢哈，>_<

[msg7086]

@binjjam 是啊要魔改 SQL 模式。

[phx13ye]

select 非 group by 字段应该怎么处理最好，除了改 sql_mode

[IzumiKoushiro]

万分感谢啦！