mysql group by

mysql group by 的 奇怪地方？ 是bug 还是它的特性

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7397 to server version: 5.1.31

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use nowhill;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test(a int,b int,c int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(1,1,1),(1,1,2),(1,1,3),(1,2,5),(1,2,1),(1,2,6);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1,1),(1,1,2),(1,1,3),(1,2,5),(1,2,1),(1,2,6)' at line 1
mysql> insert into test values(1,1,1),(1,1,2),(1,1,3),(1,2,5),(1,2,1),(1,2,6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    1 |    2 |
|    1 |    1 |    3 |
|    1 |    2 |    5 |
|    1 |    2 |    1 |
|    1 |    2 |    6 |
+------+------+------+
6 rows in set (0.00 sec)

mysql> select a,b,c from test group by a,b;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    5 |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from (select a,b,c from test  order by a,b,c) group by a,b;
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select * from (select a,b,c from test  order by a,b,c) b group by a,b;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
+------+------+------+
2 rows in set (0.00 sec)

select * from (select a,b,c from test  order by a,b,c) b group by a,b;
这样的sql 在orace 下是行不通的
但是 在mysql 下 起到了 意向不到的效果

相当得到了 a,b两列 分组后 的 c的 最小值  ，如果 排序相反，还可以得到最大值，

如:
mysql> select * from (select a,b,c from test  order by a,b,c desc) b group by a,b;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    3 |
|    1 |    2 |    6 |
+------+------+------+
2 rows in set (0.00 sec)

1.这个不是mysql的bug，这个是mysql对group by功能的扩展。
2.得到的不是你说的最小值和最大值之类的。

对于group by在mysql中的使用和Oracle的差异性很大，准确的说不光和Oracle和别的数据库差异性一样，这些有点不太遵循标准SQL。我们知道常规的 sql，对于group by来说一定要结合聚合函数，而且选择的字段除了聚合函数外，还必须在group by中出现，否则报错，但是在mysql中扩展了这样的功能
首先对于不加聚合函数的sql来说，它的功能结合了limit来得出结果，仔细想想的时候有点Oracle分析函数的味道，limit的时候得到的并不是 最大最小的值，而是某一下group by结果集中第一行，也就是刚才说的相当与先group by， 然后在每个group by下面进行limit 1。
其次，刚才还说了常规的group by结合聚合函数的时候，选择的字段除了聚合函数外，必须在group by中存在，但是在mysql中不是这样了，它具有隐含字段的功能，例如：

(root:im-mysql:16:34:45)[test]> select a,b,c,count(*) from test1 group by a,b;
+------+------+------+----------+
| a    | b    | c    | count(*) |
+------+------+------+----------+
|    1 |    1 |    1 |        3 |
|    1 |    2 |    5 |        3 |
+------+------+------+----------+

对于没有选择的字段，上面是c，c的值还是和上面说到的一样，是根据得到的结果集然后根据每个group by 进行limit 1得到的结果。

转载

http://blog.sina.com.cn/s/blog_6a2642390100rvzz.html

同类问题 ms sql 解决如下：

SELECT
resource_id,
doyen_id,
resource_name,
ROW_NUMBER() OVER (
PARTITION BY resource_id
ORDER BY
create_date
) AS ROW_INDEX
FROM
DOYEN_RESOURCE;