求助一个连续3天超库存的sql语句写法

Question

现有一个stock库存表，共有四个字段：日期、门店、商品、超库存次数（正整数，最小为0值），示例数据如下：日期        门店        商品        超库存次数
2011-05-01  001店       电视机      0
2011-05-01  002店       电视机      1
2011-05-01  003店       电视机      4
2011-05-02  001店       自行车      2
2011-05-02  002店       自行车      0
2011-05-03  001店       冰箱        7
2011-05-03  003店       冰箱        0
...         ...         ...         ...现在前端作了一个查询，查询条件是“开始日期、结束日期”两个条件，希望查出来的结果是：每家店每个商品的“连续三天超库次数”(只要"超库存次数"字段>0则代表当天超库了)。要求：从查询开始日进行计算，查询时要去除周六、周日，只计算周一至周五(循环计算)，如果有连续三天"超库存次数"均大于0则计为1，如此累计示例如下：查询条件是从2011.05.03至2011.06.22，则某个门店+某个商品：
2011.05.03 周二 超库存次数>0
2011.05.04 周三 超库存次数>0
2011.05.05 周四 超库存次数>0  计为1
2011.05.06 周五 超库存次数>0
2011.05.07 周六 跳过
2011.05.08 周日 跳过
2011.05.09 周一 超库存次数>0
2011.05.10 周二 超库存次数>0  计为2
2011.05.11 周三 超库存次数=0
2011.05.12 周四 超库存次数>0
2011.05.13 周五 超库存次数>0
2011.05.14 周六 跳过
2011.05.15 周日 跳过
2011.05.16 周一 超库存次数=0
2011.05.17 周二 超库存次数>0
2011.05.18 周三 超库存次数>0
2011.05.19 周四 超库存次数>0  计为3
2011.05.20 周五 超库存次数>0还请高手帮忙看下，非常感谢！

Answer 1

--生成测试数据。其中1号周日，7号周六，会被排除
if object_id('[tb]') is not null drop table [tb]
create table [tb] (日期 datetime,门店 varchar(5),商品 varchar(6),超库存次数 int)
insert into [tb]
select '2011-05-01','001店','电视机',1 union all
select '2011-05-01','002店','电视机',1 union all
select '2011-05-01','001店','自行车',1 union all
select '2011-05-01','002店','自行车',1 union all
select '2011-05-02','001店','电视机',1 union all
select '2011-05-02','002店','电视机',1 union all
select '2011-05-02','001店','自行车',1 union all
select '2011-05-02','002店','自行车',1 union all
select '2011-05-03','001店','电视机',1 union all
select '2011-05-03','002店','电视机',1 union all
select '2011-05-03','001店','自行车',1 union all
select '2011-05-03','002店','自行车',1 union all
select '2011-05-04','001店','电视机',1 union all
select '2011-05-04','002店','电视机',1 union all
select '2011-05-04','001店','自行车',1 union all
select '2011-05-04','002店','自行车',1 union all
select '2011-05-05','001店','电视机',1 union all
select '2011-05-05','002店','电视机',1 union all
select '2011-05-05','001店','自行车',1 union all
select '2011-05-05','002店','自行车',1 union all
select '2011-05-06','001店','电视机',1 union all
select '2011-05-06','002店','电视机',1 union all
select '2011-05-06','001店','自行车',1 union all
select '2011-05-06','002店','自行车',1 union all
select '2011-05-07','001店','电视机',1 union all
select '2011-05-07','002店','电视机',1 union all
select '2011-05-07','001店','自行车',1 union all
select '2011-05-07','002店','自行车',1--分组排序后的数据放入cte
;with cte as(
select rid=row_number() over (partition by 门店,商品 order by 日期),*
from [tb] where datepart(weekday,日期) not in (1,7) and 超库存次数>0
)
--按需求取条件时间内非周六周日，门店、商品相同的连续3天超库存次数大于0的
select 门店,商品,count(*) from cte a where 
exists(select 1 from cte where 门店=a.门店 and 商品=a.商品 and datediff(day,日期,a.日期)=1 and rid=a.rid-1) and
exists(select 1 from cte where 门店=a.门店 and 商品=a.商品 and datediff(day,日期,a.日期)=2 and rid=a.rid-2) and
日期>='2011-5-1' and 日期+2<='2011-5-9' --这里要注意结束时间是日期+2，不是日期
group by 门店,商品drop table [tb]--思路如上，如有细微差别，可自行更改！
/*
001店 电视机 3
002店 电视机 3
001店 自行车 3
002店 自行车 3

Answer 2

geniuswjt：多谢帮忙。不过我试了一下，似乎还有点问题。就是：如果周一至周五每天"超库存次数"都大于0，会记成3次，就是周一至周三记计1，周二至周四计2，周三至周五计3，这样就重复计算了我想实现的效果是周一至周三计1次后，下次就要从周四开始向后计算还能帮忙再看看不？

Answer 3

--5月2号周一，生成2周的测试数据，其中第二周自行车店无连续3天的
if object_id('[tb]') is not null drop table [tb]
create table [tb] (日期 datetime,门店 varchar(5),商品 varchar(6),超库存次数 int)
insert into [tb]
select '2011-05-02','001店','电视机',1 union all
select '2011-05-02','001店','自行车',1 union all
select '2011-05-03','001店','电视机',1 union all
select '2011-05-03','001店','自行车',1 union all
select '2011-05-04','001店','电视机',1 union all
select '2011-05-04','001店','自行车',1 union all
select '2011-05-05','001店','电视机',1 union all
select '2011-05-05','001店','自行车',1 union all
select '2011-05-06','001店','电视机',1 union all
select '2011-05-06','001店','自行车',1 union all
select '2011-05-07','001店','电视机',1 union all
select '2011-05-07','001店','自行车',1 union all
select '2011-05-08','001店','电视机',1 union all
select '2011-05-08','001店','自行车',1 union all
select '2011-05-09','001店','电视机',1 union all
select '2011-05-09','001店','自行车',0 union all
select '2011-05-10','001店','电视机',1 union all
select '2011-05-10','001店','自行车',0 union all
select '2011-05-11','001店','电视机',1 union all
select '2011-05-11','001店','自行车',0 union all
select '2011-05-12','001店','电视机',1 union all
select '2011-05-12','001店','自行车',0 union all
select '2011-05-13','001店','电视机',1 union all
select '2011-05-13','001店','自行车',0 union all
select '2011-05-14','001店','电视机',1 union all
select '2011-05-14','001店','自行车',0--设置周一为每周的第一天(这一点很重要！)
set datefirst 1--按周数、门店、商品分组，按日期排序，生成流水号；排除周六周日，排除超库存次数为0的
;with cte as(
select rid=row_number() over (partition by datepart(week,日期),门店,商品 order by 日期),*
from [tb] where datepart(weekday,日期) not in (1,7) and 超库存次数>0
)
--按条件取连续3天超库存次数大于0的
--1周内连续3天的只计1次，通过count(distinct datepart(week,日期))实现
select 门店,商品,count(distinct datepart(week,日期)) from cte a where 
exists(select 1 from cte where 门店=a.门店 and 商品=a.商品 and datediff(day,日期,a.日期)=1 and rid=a.rid-1) and
exists(select 1 from cte where 门店=a.门店 and 商品=a.商品 and datediff(day,日期,a.日期)=2 and rid=a.rid-2) and
日期>='2011-5-1' and 日期+2<='2011-5-14' --这里要注意结束时间是日期+2，不是日期
group by 门店,商品--其实你根据你自己的需求再改改就可以了，思路是一样的，就加个对周数的分组和判断，如上
/*
001店 电视机 2
001店 自行车 1

Answer 4

SORRY,SORRY,是我上面没说明白，自罚40分，呵呵。接上面说的，如果周一至周三记1次后，下次是周四、周五加下周一可以记为2次，这样循环累计，只要不包括周六、周日且"超库存次数"大于0的就可以累计的。

Answer 5

如果geniuswjt有时间，还请能帮忙调整下，万分感谢

Answer 6

我基本能理解是什么意思，不过对于SQL的时间函数我一直不太开窍，一看到date就犯晕，呵呵。还请能多指教指教

Answer 7

--这3个函数先看下意思
datepart
datediff
row_number()
--这个了解下
set datefirst 1
--cte简单了解下
;with cte as(...)/*
先忙别的去了，你的需求自己了解上面那些东西先想想看
对了，set datefirst 1 之后我上面的代码其实有错误，自己找找看
晚点再来看