序号 日期 销量
1 2015/2/20 7
2 2015/2/21 7
3 2015/2/22 7
4 2015/2/23 7
5 2015/2/24 0
6 2015/2/25 7
7 2015/2/26 7
8 2015/2/27 0
9 2015/2/28 0
10 2015/3/1 0
11 2015/3/2 7
12 2015/3/3 7
希望能查询出的结果是
2015/2/27
2015/2/28
2015/3/1
谢谢大家，元宵节快过了，也要快乐！
------解决思路----------------------
drop table mytable
go
create table mytable (id int , cdate datetime , num int )
go
insert into mytable
values( 1, '2015/2/20' ,7) ,
(2 ,'2015/2/21' ,7),
(3 ,'2015/2/22' ,7),
(4 ,'2015/2/23' ,7),
(5 ,'2015/2/24' ,0),
(6 ,'2015/2/25' ,7),
(7 ,'2015/2/26' ,7),
(8 ,'2015/2/27' ,0),
(9 ,'2015/2/28' ,0),
(10 ,'2015/3/1' ,0),
(11 ,'2015/3/2' ,7),
(12 ,'2015/3/3' ,7)
go
with mt as (
select * , cdate + ROW_NUMBER() over(order by id desc) add_date
from mytable where num = 0
)
select id , cdate , num from mt
where add_date in (select add_date from mt group by add_date having COUNT(*) >=3)
go
(12 行受影响)
id cdate num
----------- ----------------------- -----------
10 2015-03-01 00:00:00.000 0
9 2015-02-28 00:00:00.000 0
8 2015-02-27 00:00:00.000 0
(3 行受影响)
------解决思路----------------------
;WITH CTE AS(
SELECT *,ROW_NUMBER()OVER(ORDER BY 日期)RN1
FROM mytable
)
,CTE2 AS(
SELECT *,ROW_NUMBER()OVER(ORDER BY 日期)RN2
FROM CTE
WHERE 销量=0
)
,CTE3 AS(
SELECT *,COUNT(1)OVER(PARTITION BY RN1-RN2)C
FROM CTE2
)
SELECT 序号,日期 FROM CTE3
WHERE C=3