求助一个查询连续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
还请高手帮忙看下，非常感谢！
最佳答案
0
写存储过程吧，先将记录排个序，然后循环所有的记录，自己来计算超出库存的计数。
收获园豆：150
--这样查可以了
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
完全正确，太谢谢了
其他回答(6)
1
你要是把建表语句还有数据丢出来 还可以试试 不然谁那么人才
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
多谢你的建议，我一会试试
试过了朋友，结果也不对。查不出数据来
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
我测试过，结果不对
0
你好,
先分析下你的源数据表的格式是
超库存表 { 日期, 门店, 商品, 超库存次数 }
请问有为源表设置集聚索引(主键)吗?
然后你在说一下你具体需要得到的结果表的列数及他们的列名具体是什么要求?
毕竟
结果表 { 日期, 星期N, 超库存次数 }
这个应该不是你想要的结果表的规格!
收获园豆：5
0
http://blog.loveyuki.com/Article/199.aspx
用 CTE 的递归
收获园豆：5
0
请问某个门店+某个商品是指定的吗？
