一个表 记录 oneDay twoDay ... 20Day 记录20天的值 如何去掉或者筛选连续7天为0 的数据？

[FENG]

一个表 记录 oneDay twoDay ... 20Day ， 共20个字段 记录20天的值， 如何去掉或者筛选连续7天为0 的数据？（起始位置不定）

[zbdzjx]

大致写法：

select * from table1 where id not in (
select id from (
select id,1day, 2day, 3day,4day,5day,6day,7day from table1
union all
select id, 2day, 3day,4day,5day,6day,7day,8day from table1
union all
select id, 3day,4day,5day,6day,7day,8day, 9day from table1
union all
……
union all
select id,14day, 15day, 16day,17day,18day,19day,20day from table1
) aa where 1day=0 and 2day=0 and 3day=0 and 4day=0 and 5day=0 and 6day=0 and 7day=0
)

[Tiger_Zhao]

SELECT *
  FROM (-- 把每一天的零/非零转换成标志字符0/1
        SELECT *,
               (CASE WHEN [1day]=0 THEN '0' ELSE '1' END)+
               (CASE WHEN [2day]=0 THEN '0' ELSE '1' END)+
               ...
               (CASE WHEN [20day]=0 THEN '0' ELSE '1' END) mask
          FROM table1
       ) t
 WHERE CHARINDEX('0000000',mask) <> 0 -- 判断标志中是否存在连续7个0

[Haytor]

如果字段是int类型，可以参考我这个方法。

DECLARE @FindBin BINARY(28) -- 7个连续的零（4*7）
SET @FindBin = 0

;WITH BinTable AS(
SELECT 
	*,
	CONVERT(BINARY(4),[1day])+
	CONVERT(BINARY(4),[2day])+
	CONVERT(BINARY(4),[3day])+
	...
	CONVERT(BINARY(4),[20day]) AS BinData
 FROM dbo.TestChk
 )
 SELECT 
	* 
 FROM 
	BinTable
 WHERE
	CHARINDEX(@FindBin,BinData)>0

[AcHerat]

学习四五楼的方法！

[FENG]

最笨的方法 select * from tab where (1day=0 and 2day=0....and7day=0)||(2day=0 and ....8day=0)||....(14day....20day=0)

[绿豆丫]

我觉得应该先列转行了，再筛选比较好