有一张表是这样的
mysql> select * from t3 order by no,time desc
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 6 | 1 | 7 | 2016-02-26 10:17:10 |
| 4 | 1 | 11 | 2016-02-26 10:13:10 |
| 3 | 1 | 10 | 2016-02-26 10:12:10 |
| 1 | 1 | 1 | 2016-02-26 10:10:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 2 | 2 | 5 | 2016-02-26 10:11:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
| 8 | 3 | 5 | 2016-02-26 10:19:10 |
+------+------+-------+---------------------+
9 rows in set (0.00 sec)现在找出以no分组,time时间最近的数据按照我在oracle中写sql的习惯绝壁会这样写
mysql> select * from t3 where (no,time) in (select no,max(time) from t3 group by no) order by no;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+但是我在另一个帖子中看到的版主大大这样写的也可以
mysql> select id,no,value,time
-> from
-> (
-> select *
-> from t3
-> order by no,time desc
-> ) t
-> group by no;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+
3 rows in set (0.11 sec)
为何group会将每组的第一条数据查出来,这种语法结构在oracle中会报错的,是mysql的特性?
就像这样
mysql> select * from job;
+---------+--------+
| id | name |
+---------+--------+
| 1 | dba |
| 2 | java |
| 5000003 | jin |
| 1 | php |
| 2 | python |
+---------+--------+
5 rows in set (0.00 sec)mysql> select * from job group by id;
+---------+------+
| id | name |
+---------+------+
| 1 | dba |
| 2 | java |
| 5000003 | jin |
+---------+------+
3 rows in set (0.00 sec)
我也是醉了,这种sql也不报错???是mysql的什么特性吗?
mysql> select * from t3 order by no,time desc
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 6 | 1 | 7 | 2016-02-26 10:17:10 |
| 4 | 1 | 11 | 2016-02-26 10:13:10 |
| 3 | 1 | 10 | 2016-02-26 10:12:10 |
| 1 | 1 | 1 | 2016-02-26 10:10:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 2 | 2 | 5 | 2016-02-26 10:11:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
| 8 | 3 | 5 | 2016-02-26 10:19:10 |
+------+------+-------+---------------------+
9 rows in set (0.00 sec)现在找出以no分组,time时间最近的数据按照我在oracle中写sql的习惯绝壁会这样写
mysql> select * from t3 where (no,time) in (select no,max(time) from t3 group by no) order by no;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+但是我在另一个帖子中看到的版主大大这样写的也可以
mysql> select id,no,value,time
-> from
-> (
-> select *
-> from t3
-> order by no,time desc
-> ) t
-> group by no;
+------+------+-------+---------------------+
| id | no | value | time |
+------+------+-------+---------------------+
| 7 | 1 | 3 | 2016-02-26 10:18:10 |
| 5 | 2 | 8 | 2016-02-26 10:14:10 |
| 9 | 3 | 1 | 2016-02-26 10:20:10 |
+------+------+-------+---------------------+
3 rows in set (0.11 sec)
为何group会将每组的第一条数据查出来,这种语法结构在oracle中会报错的,是mysql的特性?
就像这样
mysql> select * from job;
+---------+--------+
| id | name |
+---------+--------+
| 1 | dba |
| 2 | java |
| 5000003 | jin |
| 1 | php |
| 2 | python |
+---------+--------+
5 rows in set (0.00 sec)mysql> select * from job group by id;
+---------+------+
| id | name |
+---------+------+
| 1 | dba |
| 2 | java |
| 5000003 | jin |
+---------+------+
3 rows in set (0.00 sec)
我也是醉了,这种sql也不报错???是mysql的什么特性吗?
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式
(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在
GROUP BY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外, SELECT语句中的每个列都必须在GROUP BY子
句中给出。