首页 新闻 搜索 专区 学院

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

1
悬赏园豆:200 [已解决问题] 解决于 2011-09-29 10:39

现有一个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次,注意这个是跳过周六日后,上周五 +本周一 +本周二也算作1次
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次,前面几个都没有连续3天
2011.05.20 周五 超库存次数>0

还请高手帮忙看下,非常感谢!

ok8209的主页 ok8209 | 初学一级 | 园豆:25
提问于:2011-09-06 08:53
< >
分享
最佳答案
0

写存储过程吧,先将记录排个序,然后循环所有的记录,自己来计算超出库存的计数。

收获园豆:150
dail | 小虾三级 |园豆:630 | 2011-09-07 17:24

--这样查可以了 

DECLARE @ShopGoods  VARCHAR(100)
DECLARE @StockCount  INT
DECLARE @TempGoods  VARCHAR(100)
DECLARE @TempCount  INT
SET @TempCount=0

DECLARE  @StockTable TABLE (ShopGoods varchar(100),ExceedStock INT)


DECLARE CurTriExceed CURSOR
FOR
SELECT  shop + '|' + Goods,
        StockCount
FROM   testStock ts
WHERE  ( DATENAME(dw, CreateTime) <> 'sunday'
              AND DATENAME(dw, CreateTime) <> 'Saturday'
            )
ORDER BY shop,goods,createtime

OPEN CurTriExceed
 
FETCH CurTriExceed INTO @ShopGoods,@StockCount

WHILE @@FETCH_STATUS=0
BEGIN
 
 --第一次统计
 IF @TempGoods<>@ShopGoods
 BEGIN
  SET @TempCount=0
  --SET @ExceedStockSum=0
 END
 
 --统计超出库存的数量 
 IF @StockCount>0
  SET @TempCount = @TempCount + 1
 ELSE
  SET @TempCount=0
 
 
 --超出3日库存的合计
 IF @TempCount=3
 BEGIN
  --SET @ExceedStockSum = @ExceedStockSum +1
  --初始化重新计算
  SET @TempCount=0

  INSERT INTO @StockTable VALUES(@TempGoods,1)
 END 

 SET @TempGoods = @ShopGoods

 FETCH CurTriExceed INTO @ShopGoods,@StockCount
END


IF @TempGoods<>@ShopGoods
BEGIN
 SET @TempCount=0
END
 
--统计超出库存的数量 
IF @StockCount>0
 SET @TempCount = @TempCount + 1
ELSE
 SET @TempCount=0

--超出3日库存的合计
IF @TempCount=3
BEGIN
 INSERT INTO @StockTable VALUES(@ShopGoods,1)
END 

CLOSE CurTriExceed
DEALLOCATE CurTriExceed

SELECT
substring(ShopGoods,1, (charindex('|',ShopGoods)-1)) as Shop,
substring(ShopGoods, (charindex('|',ShopGoods)+1),len(ShopGoods)) as Goods,
ExceedStock
FROM

SELECT
ShopGoods,SUM(ExceedStock) ExceedStock FROM @StockTable
GROUP BY ShopGoods
) Temp

chinaagan | 园豆:205 (菜鸟二级) | 2011-09-08 11:35

完全正确,太谢谢了

ok8209 | 园豆:25 (初学一级) | 2011-09-29 10:40
其他回答(6)
1

你要是把建表语句还有数据丢出来 还可以试试  不然谁那么人才

sk_insomina | 园豆:138 (初学一级) | 2011-09-06 14:50
0

select Department,Product,orderflag from ( select Department,Product,code+row_number()over( order by code desc) orderflag from (select * from (select *,row_number()over( order by date desc)code from stock s where datename(weekday,s.date)!='星期日' or datename(weekday,s.date)!='星期六' ) s where outcount!=0 )r )t group by Department,Product,orderflag having count(orderflag)>=3

没有测试,思路:code:9,8,7,6对应行号1,2,3,4,相加都是10,那么如果删去8。

9,7,6 对应行号1,2,3相加结果是:10,9,9。结论连续的相加结果相同。

收获园豆:20
HHSoft | 园豆:150 (初学一级) | 2011-09-06 14:56
多谢你的建议,我一会试试
支持(0) 反对(0) ok8209 | 园豆:25 (初学一级) | 2011-09-06 16:08
试过了朋友,结果也不对。查不出数据来
支持(0) 反对(0) ok8209 | 园豆:25 (初学一级) | 2011-09-06 21:47
0

试试这个sql文

--日期  CreateTime
--门店 shop 
--商品 goods
--超库存次数 PerCount
SELECT temp.shop,temp.goods,ceiling(COUNT(1)*1.0/3) AS PerCount
FROM
(       
 SELECT  CreateTime,shop,Goods,StockCount
 FROM    testStock ts
 WHERE   stockCount > 0 AND (DATENAME(dw,CreateTime)<>'sunday' AND DATENAME(dw,CreateTime)<>'Saturday')
   AND EXISTS ( SELECT 1
       FROM   testStock
       WHERE  stockCount > 0
        AND ts.goods = goods
        AND
        (
         DATEDIFF(DAY, ts.CreateTime, CreateTime) = 1
         OR
         (
          DATENAME(dw,CreateTime)='Saturday' AND DATEDIFF(DAY, ts.CreateTime, CreateTime)=3
         )
        )
        )
   AND EXISTS ( SELECT 1
       FROM   testStock
       WHERE  stockCount > 0
        AND ts.goods = goods
        AND
        (
         DATEDIFF(DAY, ts.CreateTime, CreateTime) = 2
         OR
         (
          DATENAME(dw,CreateTime)='sunday' AND DATEDIFF(DAY, ts.CreateTime, CreateTime)=4
         )
        )
        )     
)
temp
GROUP BY temp.shop,temp.goods

收获园豆:20
chinaagan | 园豆:205 (菜鸟二级) | 2011-09-06 16:57
我测试过,结果不对
支持(0) 反对(0) ok8209 | 园豆:25 (初学一级) | 2011-09-06 21:44
0

你好,

先分析下你的源数据表的格式是

超库存表 { 日期, 门店, 商品, 超库存次数 }

请问有为源表设置集聚索引(主键)吗?

 

然后你在说一下你具体需要得到的结果表的列数及他们的列名具体是什么要求?

毕竟

结果表 { 日期, 星期N, 超库存次数 }

这个应该不是你想要的结果表的规格!

收获园豆:5
dotNetDR_ | 园豆:2058 (老鸟四级) | 2011-09-08 16:29
0

http://blog.loveyuki.com/Article/199.aspx

用 CTE 的递归

收获园豆:5
Loveyuki | 园豆:205 (菜鸟二级) | 2011-09-09 14:50
0

请问某个门店+某个商品是指定的吗?

释梦 | 园豆:215 (菜鸟二级) | 2011-09-16 12:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册