根据一列数据的值，数据的连续性判断

[nonoqiqi]

老司机来看下吧
有些逻辑不想用程序去接收再判断了，想在SQL里直接实现


表结构数据如图
id=3,5,6,7 这几条数据都是‘检修’状态，判断他们的依据是num=0
现在需求改了，id=3不再是‘检修’状态，一定要连续两次以上num=0才能判定为‘检修’状态
求SQL逻辑重新判定生成 status列

[二月十六]

看看是这个意思不，用mssql写的，把测试数据后语句中的表名换成自己的试试：

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[num] int,[status] NVARCHAR(100))
Insert #T
select 1,1,null union all
select 2,1,null union all
select 3,0,null union all
select 4,1,null union all
select 5,0,null union all
select 6,0,null
Go
--测试数据结束
SELECT  a.id ,
        a.num ,
        CASE WHEN a.num = 0
                  AND b.num = 0 THEN '检修'
             ELSE '正常'
        END AS status
FROM    #T a
        LEFT  JOIN #T b ON a.id = b.id + 1

[nonoqiqi]

你的逻辑正常，但是还是不满足我的需求 我的需求是: 只要连续2次 num=0 都视为‘检修’，当然也包含本身，也就是你的id=5这条也应该是‘检修’

[糖炒唐朝栗子]

#建表 CREATE TABLE s( id INT(10) PRIMARY KEY , datelist DATETIME, num INT(2)); #插数 INSERT INTO s VALUES (1,'2017-08-01 12:00:00',1), (2,'2017-08-01 12:05:00',1),(3,'2017-08-01 12:10:00',0), (4,'2017-08-01 12:15:00',1),(5,'2017-08-01 12:20:00',0), (6,'2017-08-01 12:25:00',0),(7,'2017-08-01 12:30:00',0), (8,'2017-08-01 12:35:00',1),(9,'2017-08-01 12:40:00',1), (10,'2017-08-01 12:45:00',1),(11,'2017-08-01 12:50:00',1), (12,'2017-08-01 12:55:00',1) #查询 SELECT a.*, CASE WHEN a.num =0 AND (b.num=0 OR c.num=0) THEN '检修' ELSE '正常' END AS stutas FROM s AS a LEFT JOIN s AS b ON a.id =b.id-1 LEFT JOIN s AS c ON a.id =c.id+1

[糖炒唐朝栗子]

[nonoqiqi]

SELECT  a.id,
        a.datelist,
        a.num,
        CASE WHEN a.num = 0 && b.num = 0 THEN '检修' ELSE '正常' END AS status
FROM    
	(SELECT
		id,datelist,
		COUNT(*)-1 AS num
	FROM
		(
			SELECT id,CONCAT('2017-08-01 12:',val) AS datelist FROM xj_num
			UNION ALL
			SELECT @i:=@i+1 AS id,FROM_UNIXTIME(collect_time,'%Y-%m-%d %H:%i:%s') AS realtime 
			FROM xj_collection_detail,(SELECT @i:=0) AS tb 
			WHERE project_id='310115ED0001' AND sensor_id=6 AND del=0 AND FROM_UNIXTIME(collect_time,'%Y-%m-%d %H')='2017-08-01 12'
		) AS tb1
	GROUP BY datelist) AS a
left JOIN 
	(SELECT
		id,datelist,
		COUNT(*)-1 AS num
	FROM
		(
			SELECT id,CONCAT('2017-08-01 12:',val) AS datelist FROM xj_num
			UNION ALL
			SELECT @i:=@i+1 AS id,FROM_UNIXTIME(collect_time,'%Y-%m-%d %H:%i:%s') AS realtime 
			FROM xj_collection_detail,(SELECT @i:=0) AS tb 
			WHERE project_id='310115ED0001' AND sensor_id=6 AND del=0 AND FROM_UNIXTIME(collect_time,'%Y-%m-%d %H')='2017-08-01 12'
		) AS tb1
	GROUP BY datelist) AS b
ON (a.id=b.id+1 && a.datelist=ADDDATE(b.datelist,INTERVAL 5 MINUTE) && a.id>b.id)

按照你的逻辑写的，执行成功没问题老铁，但是逻辑还不是我想要的


也就是图上id=5的status也是‘检修’，那逻辑就对了

[nonoqiqi]

ok left join 3次解决，结贴

[二月十六]

那就再加一个判断

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([id] int,[num] int,[status] NVARCHAR(100))
Insert #T
select 1,1,null union all
select 2,1,null union all
select 3,0,null union all
select 4,1,null union all
select 5,0,null union all
select 6,0,null
Go
--测试数据结束
SELECT  a.id ,
        a.num ,
        CASE WHEN a.num = 0
                  AND ( b.num = 0
                        OR c.num = 0
                      ) THEN '检修'
             ELSE '正常'
        END AS status
FROM    #T a
        LEFT  JOIN #T b ON a.id = b.id + 1
        LEFT  JOIN #T c ON a.id = c.id - 1