有表,有查询语句如斯者:
CREATE TABLE tbl
(
id INT,
time DATE
);INSERT INTO tbl(id, time) VALUES(1, '2009-06-07'), (1, '2009-06-07'), (1, '2009-06-07'),
(2, '2009-06-07'), (3, '2009-06-07'), (3, '2009-06-07'),
(3, '2009-06-07'), (1, '2009-06-08'), (2, '2009-06-08'),
(2, '2009-06-08'), (4, '2009-06-08'), (5, '2009-06-08');select id, time, count(id) from tbl group by id, time order by time desc;
select max(id) from tbl group by time;
select count(id) from tbl group by time;查询语句中只要有了group by, 其结果都是基于group by后的一个个小区间的统计。
可以统计各个小区间结果集的个数,最大值和最小值等。但是现在不知道如何把group by后的小区间,分着列出来!
CREATE TABLE tbl
(
id INT,
time DATE
);INSERT INTO tbl(id, time) VALUES(1, '2009-06-07'), (1, '2009-06-07'), (1, '2009-06-07'),
(2, '2009-06-07'), (3, '2009-06-07'), (3, '2009-06-07'),
(3, '2009-06-07'), (1, '2009-06-08'), (2, '2009-06-08'),
(2, '2009-06-08'), (4, '2009-06-08'), (5, '2009-06-08');select id, time, count(id) from tbl group by id, time order by time desc;
select max(id) from tbl group by time;
select count(id) from tbl group by time;查询语句中只要有了group by, 其结果都是基于group by后的一个个小区间的统计。
可以统计各个小区间结果集的个数,最大值和最小值等。但是现在不知道如何把group by后的小区间,分着列出来!
+------------+---------+---------+-----------+
| time | max(id) | min(id) | count(id) |
+------------+---------+---------+-----------+
| 2009-06-07 | 3 | 1 | 7 |
| 2009-06-08 | 5 | 1 | 5 |
+------------+---------+---------+-----------+
2 rows in set (0.00 sec)mysql>
select time,max(id),min(id), count(id) from tbl group by time;
这样?select id, time, count(id),max(id) from tbl group by id, time order by time desc;
select time,max(id) from tbl group by time;
select time,count(id) from tbl group by time;
-> union all
-> select id,time,null as smax,null as smin,count(id) from tbl group by id,time
-> union all
-> select null as id,time,max(id),min(id), count(id) from tbl group by time
-> order by time,isnull(id),id,isnull(scnt);
+------+------------+------+------+------+
| id | time | smax | smin | scnt |
+------+------------+------+------+------+
| 1 | 2009-06-07 | NULL | NULL | 3 |
| 1 | 2009-06-07 | NULL | NULL | NULL |
| 1 | 2009-06-07 | NULL | NULL | NULL |
| 1 | 2009-06-07 | NULL | NULL | NULL |
| 2 | 2009-06-07 | NULL | NULL | 1 |
| 2 | 2009-06-07 | NULL | NULL | NULL |
| 3 | 2009-06-07 | NULL | NULL | 3 |
| 3 | 2009-06-07 | NULL | NULL | NULL |
| 3 | 2009-06-07 | NULL | NULL | NULL |
| 3 | 2009-06-07 | NULL | NULL | NULL |
| NULL | 2009-06-07 | 3 | 1 | 7 |
| 1 | 2009-06-08 | NULL | NULL | 1 |
| 1 | 2009-06-08 | NULL | NULL | NULL |
| 2 | 2009-06-08 | NULL | NULL | 2 |
| 2 | 2009-06-08 | NULL | NULL | NULL |
| 2 | 2009-06-08 | NULL | NULL | NULL |
| 4 | 2009-06-08 | NULL | NULL | 1 |
| 4 | 2009-06-08 | NULL | NULL | NULL |
| 5 | 2009-06-08 | NULL | NULL | 1 |
| 5 | 2009-06-08 | NULL | NULL | NULL |
| NULL | 2009-06-08 | 5 | 1 | 5 |
+------+------------+------+------+------+
21 rows in set (0.00 sec)mysql>
嗯,通过ACMAIN_CHM 的回复说明,我确实没说清楚。整个表的内容:mysql> select * from tbl;
+------+------------+
| id | time |
+------+------------+
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 2 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |
| 1 | 2009-06-08 |
| 2 | 2009-06-08 |
| 2 | 2009-06-08 |
| 4 | 2009-06-08 |
| 5 | 2009-06-08 |
+------+------------+
我现在打算按照time分组(group by time)
得到这样的结果:2009-06-07 的一堆
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 1 | 2009-06-07 |
| 2 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |
| 3 | 2009-06-07 |2009-06-08的一堆
| 1 | 2009-06-08 |
| 2 | 2009-06-08 |
| 2 | 2009-06-08 |
| 4 | 2009-06-08 |
| 5 | 2009-06-08 |...
就是让它们都分成一堆一堆显示!
能否用group by 实现呢?我的理解是,在group by的操作过程中,mysql内部先将所有数据分组,然后再将每组的数据进行诸如max,min,count的统计!我在想,能否将group by在第一时间分组的结果显示出来呢?
select id, time,id1 from tbl group by id, time,id1 order by time,id
select id, time from tbl group by id, time,id1 order by time,id
不是标准的SQL语句
-> union all
-> select distinct null,time,'的一堆' as sk from tbl
-> order by time,id;
+------+------------+--------+
| id | time | sk |
+------+------------+--------+
| NULL | 2009-06-07 | 的一堆 |
| 1 | 2009-06-07 | |
| 1 | 2009-06-07 | |
| 1 | 2009-06-07 | |
| 2 | 2009-06-07 | |
| 3 | 2009-06-07 | |
| 3 | 2009-06-07 | |
| 3 | 2009-06-07 | |
| NULL | 2009-06-08 | 的一堆 |
| 1 | 2009-06-08 | |
| 2 | 2009-06-08 | |
| 2 | 2009-06-08 | |
| 4 | 2009-06-08 | |
| 5 | 2009-06-08 | |
+------+------------+--------+
14 rows in set (0.00 sec)mysql>
其实我是想加深对group by的理解!
在发这个帖子的时候,一边想一边写,想的东西变了,写得东西好像也变了!
到最后我自己想问什么也不知道了!总的来说,我的初衷就是想求证mysql 执行group by的过程!
算了,这么问吧,带有group by的sql在执行过程中是不是先把所有的记录打散成一个一个的小集合,然后再针对这些小集合进行统计?比如:select id, time, count(id) from tbl where id > 5 group by id, time order by time desc;
在执行过程中分如下几步:
1, 先筛选出id>5的所有记录,
2, 然后把这些记录按照id, time打散成一个一个的小集合,
3, 计算每一个小集合的count(id)
4, 进行排序order by time desc
5, 列出结果
from tbl where id >5
group by id, time
order by time desc;
在执行过程中分如下几步:
1, 先筛选出id>5的所有记录,
正确2, 然后把这些记录按照id, time打散成一个一个的小集合,
不知道你所谓的 "打散成一个一个的小集合" 是什么个意思。
MYSQL会把第一步取得的记录,按照 id, time 排序,这个在第一步的时候就会利用索引进行了。并且如果你存在 time, id 索引的话,这个(time,id)应该被优先使用。3, 计算每一个小集合的count(id)
没什么小集合,只有一个第一步产生的集合(排好序的),从首到尾进行计算。4, 进行排序order by time desc
如果有 (time,id) 则产生的结果是已经排好序的了。如果没有相应的索引,则利用临时表或临时文件进行排序。5, 列出结果
正确
在执行过程中分如下几步:
1、 从表中先筛选出id>5的所有记录;
2、将相同的id, time合并成一条记录,在临时表中计算count(id);
3、生成SELECT id, time, count(id)内容
4、 进行排序order by time desc
5、 列出结果
这种说法不准确。 在MYSQL的官方文档中,或者《数据库系统概论》的优化执行部分有这方面的介绍。建议有空的时候可以参考一下。
SQL codemysql> select time,max(id),min(id), count(id) from tbl group by time;
+------------+---------+---------+-----------+
| time | max(id) | min(id) | count(id) |
(
id INT,
time DATE
);INSERT INTO tbl(id, time) VALUES(1, '2009-06-07'), (1, '2009-06-07'), (1, '2009-06-07'),
(2, '2009-06-07'), (3, '2009-06-07'), (3, '2009-06-07'),
(3, '2009-06-07'), (1, '2009-06-08'), (2, '2009-06-08'),
(2, '2009-06-08'), (4, '2009-06-08'), (5, '2009-06-08');select id, time, count(id) from tbl group by id, time order by time desc;
select max(id) from tbl group by time;
select count(id) from tbl group by time;
这样就可以了