mysql中如何先排序，后分组

TEMP表有两列，id，状态（status），时间（time）数值如下（按时间排列）：
1，8,2011-06-12 12:13:13
1，8,2011-06-12 12:14:13
1，9,2011-06-12 13:11:13
1，8,2011-06-12 14:23:23
2，3,2011-06-12 15:12:12

希望得到如下结果：
id,status,min(time),max(time)
1，8,2011-06-12 12:13:13，2011-06-12 12:14:13
1，9,2011-06-12 13:11:13, 2011-06-12 13:11:13
1，8,2011-06-12 14:23:23, 2011-06-12 14:23:23
2，3,2011-06-12 15:12:12, 2011-06-12 15:12:12

下面的sql语句得到的却不是这个结果
sql语句：
select id,status,min(time),max(time) from temp group by id,status order by time;

结果：
id,status,min(time),max(time)
1，8,2011-06-12 12:13:13，2011-06-12 14:23:23
1，9,2011-06-12 13:11:13, 2011-06-12 13:11:13
2，3,2011-06-12 15:12:12, 2011-06-12 15:12:12

这个结果是先分组然后排序的，怎么才能得到希望的结果呢先排序后分组。

27 个解决方案

#1

你期望的结果完全不知道是按什么分的组

#2

不知道你想期望什么，你最起码告诉别人你的逻辑

#3

即使先排序后分组，按id，status分组后的记录也和你上面的错误结果一样的。
要想得到你想要的正确结果，同时按照id,status,time（针对你的数据，time分组时要截取到月，即按年月分组，
去掉时间）分组，就是你想要的结果

#4

引用 3 楼 hoohoney 的回复:

即使先排序后分组，按id，status分组后的记录也和你上面的错误结果一样的。
要想得到你想要的正确结果，同时按照id,status,time（针对你的数据，time分组时要截取到月，即按年月分组，
去掉时间）分组，就是你想要的结果

没有其他的方法吗？这个例子是可以把时间截取到月，但是我的项目中用到的时间最小是到秒，有没有更好的方法。

#5

详细说明，格式是否固定 ，两列时间相差1秒？没有唯一标识的字段？

#6

SELECT id,STATUS,MIN(TIME),MAX(TIME) FROM 
(
SELECT id,STATUS,TIME,(SELECT COUNT(*) FROM temp b WHERE b.time<=a.time) cnt1,
                      (SELECT COUNT(*) FROM temp c WHERE c.time>=a.time AND a.id=c.id AND a.`status`=c.status)cnt2
 FROM temp a
) d 
GROUP BY id,STATUS,cnt1+cnt2 
ORDER BY MIN(TIME);

#7

mysql> SELECT * FROM temp;
+------+--------+---------------------+
| id   | STATUS | TIME                |
+------+--------+---------------------+
|    1 |      8 | 2011-06-12 12:14:13 |
|    1 |      9 | 2011-06-12 13:11:13 |
|    1 |      8 | 2011-06-12 14:23:23 |
|    2 |      3 | 2011-06-12 15:12:12 |
|    1 |      8 | 2011-06-12 12:13:13 |
+------+--------+---------------------+
5 ROWS IN SET (0.00 sec)

mysql> SELECT id,STATUS,MIN(TIME),MAX(TIME) FROM
    -> (
    -> SELECT id,STATUS,TIME,(SELECT COUNT(*) FROM temp b WHERE b.time<=a.time)
cnt1,
    ->                       (SELECT COUNT(*) FROM temp c WHERE c.time>=a.time A
ND a.id=c.id AND a.`status`=c.status)cnt2
    ->  FROM temp a
    -> ) d
    -> GROUP BY id,STATUS,cnt1+cnt2
    -> ORDER BY MIN(TIME);
+------+--------+---------------------+---------------------+
| id   | STATUS | MIN(TIME)           | MAX(TIME)           |
+------+--------+---------------------+---------------------+
|    1 |      8 | 2011-06-12 12:13:13 | 2011-06-12 12:14:13 |
|    1 |      9 | 2011-06-12 13:11:13 | 2011-06-12 13:11:13 |
|    1 |      8 | 2011-06-12 14:23:23 | 2011-06-12 14:23:23 |
|    2 |      3 | 2011-06-12 15:12:12 | 2011-06-12 15:12:12 |
+------+--------+---------------------+---------------------+
4 ROWS IN SET (0.01 sec)

mysql>

#8

看楼主的意思应该是仅对时间上相邻,并且id和stauts相同的数据进行分组,要做到这点,就必须先对数据进行排序.
首先按时间进行排序得到所有数据的时间序号,再对同id及status的数据按时间进行逆向排序,再使用id,status,两个序号的和进行分组,就可以得到数据.

#9

谢谢楼上的朋友，方法可以用，但是数据量大花费的时间就太长了。我打算用存储过程，希望能减少花费的时间。

#10

1，8,2011-06-12   12:13:13 
1，8,2011-06-12   12:14:13 
1，8,2011-06-12   12:15:13 
1，9,2011-06-12   13:11:13 
1，8,2011-06-12   14:23:23 
2，3,2011-06-12   15:12:12

这种情况如何处理

#11

如果支持分析函数的话估计会快点.

#12

10楼得什么意思

引用 11 楼 minitoy 的回复:

如果支持分析函数的话估计会快点.

能不能用存储过程来完成呢？这是我的一个想法，不过我对写存储过程没有用过不会写。

#13

不见的会快.
你可以测试下.
建议在time列建个索引.

引用 12 楼 xiang1115 的回复:

10楼得什么意思


引用 11 楼 minitoy 的回复:
如果支持分析函数的话估计会快点.


能不能用存储过程来完成呢？这是我的一个想法，不过我对写存储过程没有用过不会写。

#14

引用 10 楼 wwwwa 的回复:

1，8,2011-06-12   12:13:13 
1，8,2011-06-12   12:14:13 
1，8,2011-06-12   12:15:13 
1，9,2011-06-12   13:11:13 
1，8,2011-06-12   14:23:23 
2，3,2011-06-12   15:12:12

这种情况如何处理

如果是这样的记录，要求结果是什么 

#15

引用 14 楼 wwwwa 的回复:

引用 10 楼 wwwwa 的回复:
1，8,2011-06-12 12:13:13
1，8,2011-06-12 12:14:13
1，8,2011-06-12 12:15:13
1，9,2011-06-12 13:11:13
1，8,2011-06-12 14:23:23
2，3,2011-06-12 15:12:12

这种情况如何处理

如果是这样的记录，要求结果是什么……

结果：
1，8,2011-06-12 12:13:13，2011-06-12 12:15:13
1，9,2011-06-12 13:11:13，2011-06-12 13:11:13
1，8,2011-06-12 14:23:23，2011-06-12 14:23:23
2，3,2011-06-12 15:12:12，2011-06-12 15:12:12

#16

SET @a=0;
SET @num=0;
SELECT id,`status`,bz,MAX(`time`),MIN(`time`) FROM (
SELECT *,@num:=IF(@a=`status`,@num,@num+1) AS bz,@a:=`status` FROM qqw) a GROUP BY id,`status`,bz;

#17

引用 16 楼 wwwwb 的回复:

SET @a=0;
SET @num=0;
SELECT id,`status`,bz,MAX(`time`),MIN(`time`) FROM (
SELECT *,@num:=IF(@a=`status`,@num,@num+1) AS bz,@a:=`status` FROM qqw) a GROUP BY id,`status`,bz;

能解释一下这个语句的意思吗？

#18

哪个地方不明白

#19

我看了半天没看懂LZ要什么，
貌似不是分组排序的问题，还涉及status字段的判断吧。
按照分组和排序的做法，我只能得到：
ID;"STATUS";"MIN(TIME)";"MAX(TIME)"
======================================
1;"8";"2011-06-12 12:13:13";"2011-06-12 14:23:23"
2;"3";"2011-06-12 15:12:12";"2011-06-12 15:12:12"

我的SQL
[SQL]
SELECT * FROM TEMP;
SELECT T1.ID ,T1.`STATUS`,T1.`TIME` AS `MIN(TIME)` ,T2.`TIME` AS `MAX(TIME)` 
FROM (select * from TEMP WHERE (id,`time`) in (select id,min(`time`) from TEMP group by id)) T1,
(select * from TEMP WHERE (id,`time`) in (select id,max(`time`) from TEMP group by id)) T2 
WHERE T1.ID = T2.ID;
[/SQL]

#20

引用 18 楼 wwwwb 的回复:

哪个地方不明白

SET @a=0;
SET @num=0;
SELECT id,`status`,bz,MAX(`time`),MIN(`time`) FROM (
SELECT *,@num:=IF(@a=`status`,@num,@num+1) AS bz,@a:=`status` FROM qqw) a GROUP BY id,`status`,bz;
这个语句为什么不能再mysql中执行呢，@num:=IF(@a=`status`,@num,@num+1) AS bz,@a:=`status` FROM qqw这是什么意思。
怎么能运行这个语句。

#21

在SP中运行
OR
在MYSQL命令行下运行

#22

引用 21 楼 wwwwb 的回复:

在SP中运行
OR
在MYSQL命令行下运行

我是要在程序代码中使用的，但是不能用，哎。



大家帮忙看看有没有更好的方法解决问题呢。

#23

引用 22 楼 xiang1115 的回复:

引用 21 楼 wwwwb 的回复:
在SP中运行
OR
在MYSQL命令行下运行

我是要在程序代码中使用的，但是不能用，哎。



大家帮忙看看有没有更好的方法解决问题呢。

你生成SP，在代码中调用不行？

#24

引用 23 楼 wwwwa 的回复:

引用 22 楼 xiang1115 的回复:
引用 21 楼 wwwwb 的回复:
在SP中运行
OR
在MYSQL命令行下运行

我是要在程序代码中使用的，但是不能用，哎。



大家帮忙看看有没有更好的方法解决问题呢。

你生成SP，在代码中调用不行？

我冒昧的问一下，sp是什么，怎么生成啊。

#25

存储过程

#26

先排序，后分组
SELECT * FROM (SELECT name, value FROM table ORDER BY value ASC) AS O GROUP BY O.name   

=_=...

#27

引用 26 楼 ci1699 的回复:

先排序，后分组
SELECT * FROM (SELECT name, value FROM table ORDER BY value ASC) AS O GROUP BY O.name  

=_=...

这个是先排序后分组，很简单，但是实现不了我要的结果。