字段取值仅为0和1，如何统计该字段在某时间段内发生变化的次数呢？

Question

intime          switch
10:00:01          0
10:00:02          0
10:00:03          1
10:00:04          1
10:00:05          0
10:00:06          1
10:00:07          0
10:00:08          1要统计出在此时间段内，switch从0变到1的次数（这里应该为3）和从1变回0的次数（这里应该为2）怎么写这个SQL语句？

Answer 1

#如果intime字段相差都是1秒，假设表名为new
select count(*) as "0--1"
from new 
 where exists (select 1 from new n where new.switch>n.switch and    new.intime=n.intime+1);select count(*) as "1--0"
from new 
 where exists (select 1 from new n where new.switch<n.switch and    new.intime=n.intime+1);

Answer 2

相差多少不考虑：
SELECT SWITCH,COUNT(DISTINCT INTIME) FROM (
SELECT A.* FROM TTHJ A 
LEFT JOIN TTHJ B ON A.switch<>B.switch AND A.intime>=B.intime
WHERE A.SWITCH='0' AND A.intime<>(SELECT MAX(INTIME) FROM TTHJ)
AND B.intime IS NOT NULL
UNION ALL
SELECT A.* FROM TTHJ A 
LEFT JOIN TTHJ B ON A.switch<>B.switch AND A.intime>=B.intime
WHERE A.SWITCH='1' AND A.intime<>(SELECT MAX(INTIME) FROM TTHJ)
AND B.intime IS NOT NULL
) A GROUP BY SWITCH

Answer 3

--创建视图，如下create view v_switch 
as 
select *,
       (select switch from test where intime>a.intime order by intime limit 1) as next_switch 
from test a ;--视图图的形式如下，也就是将switch的下一个switch值显示到next_switch列上.+---------------------+--------+-------------+
| intime              | switch | next_switch |
+---------------------+--------+-------------+
| 2008-10-20 18:04:03 |      1 |           0 | 
| 2008-10-20 18:04:10 |      0 |           1 | 
| 2008-10-20 18:04:12 |      1 |           0 | 
| 2008-10-20 18:04:14 |      0 |           1 | 
| 2008-10-20 18:04:16 |      1 |           1 | 
| 2008-10-20 18:04:18 |      1 |           0 | 
| 2008-10-20 18:04:20 |      0 |           0 | 
| 2008-10-20 18:04:22 |      0 |        NULL | 
+---------------------+--------+-------------+--然后统计你需要的值就方便多了如下；--0变1mysql> select count(*) from v_switch where switch=0 and next_switch =1; --1变0mysql> select count(*) from v_switch where switch=1 and next_switch =0;--如果只要一个SQL语句，那么只要把视图变成一个内嵌的语句即可，如下:select count(*) 
from (  select *,
               (select switch from test where intime>a.intime order by intime limit 1) as next_switch 
        from test a
     ) t
where switch=0 and next_switch =1;

Answer 4

感谢各位的回复。我试了一下，都能满足要求。唯一的问题是太耗时，我模拟数据30W条，执行时间已经无法接受。而实际的表有5kW条记录，不知道有没有更快的方法？如果没有的话，看来只好该结构了。

Answer 5

在intime字段上建立索引再试试!

Answer 6

我觉得搞SQL语句读的效率的确不敢恭维！
还不如在插这条数据的时候记录一下，建立摘要表！或者把数据读出来，在程序里面做判断！

Answer 7

select bz,count(*) from (
SELECT a.intime,0 as bz FROM TTHJ A
LEFT JOIN TTHJ B ON A.intime>B.intime
left join (SELECT MAX(INTIME) as ma FROM TTHJ) b1 on a.intime=b1.ma
left join
(SELECT a.intime FROM TTHJ A
left join tthj b on a.switch=b.switch and a.intime=b.intime+1
where b.intime is not null) c1
on a.intime=c1.intime
WHERE A.SWITCH='1' and b.switch='0' and c1.intime is null
group by a.intimeunion
SELECT a.intime,1 FROM TTHJ A
LEFT JOIN TTHJ B ON A.intime>B.intime
left join (SELECT MAX(INTIME) as ma FROM TTHJ) b1 on a.intime=b1.ma
left join
(SELECT a.intime FROM TTHJ A
left join tthj b on a.switch=b.switch and a.intime=b.intime+1
where b.intime is not null) c1
on a.intime=c1.intime
WHERE A.SWITCH='0' and b.switch='1' and c1.intime is null
group by a.intime) d1
group by bz建议增加自增ID字段，否则只有用a.intime=b.intime+1来判断是否有连续重复的0、1了，测试通过，在
INTIME、SWITCH上建立索引，不需要建立VIEW，看看速度如何。
OR 直接将WHERE A.SWITCH='1' and b.switch='0'写入在SQL语句的FROM 表名中。