mysql 下group by 操作
mysql> select *from t;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 1 | aaa |
| 1 | aaa |
| 1 | bbb |
+------+------+
6 rows in set (0.00 sec)
mysql> select id,name,count(*)from t group by id having count(1)>1;
+------+------+----------+
| id | name | count(*) |
+------+------+----------+
| 1 | aaa | 4 |
+------+------+----------+
1 row in set (0.15 sec)
oracle 下面的group 操作
SQL> select *from t;
ID NAME
---------- --------------------
1 aaa
1 aaa
1 aaa
1 bbbb
2 bbbb
SQL> select id,name,count(*)from t group by id having count(1)>1;
select id,name,count(*)from t group by id having count(1)>1
mysql> select *from t;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 1 | aaa |
| 1 | aaa |
| 1 | bbb |
+------+------+
6 rows in set (0.00 sec)
mysql> select id,name,count(*)from t group by id having count(1)>1;
+------+------+----------+
| id | name | count(*) |
+------+------+----------+
| 1 | aaa | 4 |
+------+------+----------+
1 row in set (0.15 sec)
oracle 下面的group 操作
SQL> select *from t;
ID NAME
---------- --------------------
1 aaa
1 aaa
1 aaa
1 bbbb
2 bbbb
SQL> select id,name,count(*)from t group by id having count(1)>1;
select id,name,count(*)from t group by id having count(1)>1
ORA-00979: 不是 GROUP BY 表达式
通过对两个sql的对比操作可以看出,在mysql下面对两个列进行分组操作,主要是针对group by 后面的列进行操作,其他列只是作为展示,并且满足条件的才展示结果。
在oracle里,必须对select 列的字段进行分组,展示全部分组内容,否则就会报错