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这个结果是先分组然后排序的,怎么才能得到希望的结果呢先排序后分组。
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这个结果是先分组然后排序的,怎么才能得到希望的结果呢先排序后分组。
解决方案 »
- SQL触发器 自动更新子表
- Mysql查询问题。。。
- select username=str from cdb_members where uid=1;的问题
- 我在mysql的某个中表中会不断插入连续的数据,但是由于出错的发生,导致一些数据没有插进去,现在想用一个sql语句来查查到底有哪些数据没插进去
- 一个两表链接的问题
- 学习MYSQL有什么入门经典书籍
- mysql更新数据库, 错误 42000
- 请问在win2000下如何启动mysql?
- 如何实现多行数据在某一行某个字段合计其他为0
- 新人求助基础的MySQL问题
- help!!!用mysql建立工厂物资管理数据库、、、
- mysql输入sql错了怎么更正?
要想得到你想要的正确结果,同时按照id,status,time(针对你的数据,time分组时要截取到月,即按年月分组,
去掉时间)分组,就是你想要的结果
(
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);
+------+--------+---------------------+
| 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>
首先按时间进行排序得到所有数据的时间序号,再对同id及status的数据按时间进行逆向排序,再使用id,status,两个序号的和进行分组,就可以得到数据.
你可以测试下.
建议在time列建个索引.
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;
貌似不是分组排序的问题,还涉及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]
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这是什么意思。
怎么能运行这个语句。
OR
在MYSQL命令行下运行
SELECT * FROM (SELECT name, value FROM table ORDER BY value ASC) AS O GROUP BY O.name =_=...