SQL每日一题(20210713)要求出同一台机器的状态转换次数，例如1001机器从，开机到关机算一次，从关机再到待机算一次，合计2次

SQL每日一题(20211009)

select MechineID, count(status) - 1 as cnt from T0713 as T
where not exists(select null
                 from T0713 as S
                 where S.ID = T.ID + 1 and S.MechineID = T.MechineID and S.status = T.status)
group by MechineID;

with t as(
select t.*,
           lag(status,1,null)over
           (partition by mechineid order by id)a
  from t0713 t)
select mechineid,
sum(case when status=t.a then 0 else 1 end)-1 cnt   
  from t 
group by mechineid;  

SELECT mechineid,COUNT(CASE WHEN status=n THEN NULL ELSE status END)-1 cnt  from(  
      SELECT id,mechineid,status,lead(status,1)over(PARTITION BY mechineid ORDER BY id)n FROM t0713) a
      GROUP BY mechineid

SELECT a.mechineid, COUNT(CASE WHEN a.status = b.status THEN NULL ELSE a.status END) - 1 cnt
FROM t0713 a
         LEFT JOIN t0713 b ON a.id - b.id = 1 AND a.mechineid = b.mechineid
GROUP BY a.mechineid;