关于存货的剩余数量

Question

本帖最后由 whisky_sour 于 2009-09-17 20:04:57 编辑

Answer 1

http://topic.csdn.net/u/20080920/15/424c77bf-7610-4888-be85-9a43e70f55c6.html?74258参考第13楼

Answer 2

IF OBJECT_ID('Order','U') IS NOT NULL DROP TABLE [Order] 
IF OBJECT_ID('Stock','U') IS NOT NULL DROP TABLE [Stock]
GO
CREATE TABLE [ORDER] (
[NO] INT,
PROD_NAME VARCHAR(20),
SHIPDATE DATETIME,
QTY NUMERIC(19,2)
)
INSERT INTO [ORDER]
SELECT 1        ,'001','2009/02/27',      100 UNION ALL
SELECT 2        ,'001','2009/03/01',      200 UNION ALL
SELECT 3        ,'001','2009/03/03',      300 UNION ALL
SELECT 4        ,'002','2009/02/04',      100 UNION ALL
SELECT 5        ,'002','2009/03/03',      200 UNION ALL
SELECT 6        ,'003','2009/04/01',      300 UNION ALL
SELECT 7        ,'004','2009/04/05',      100 
CREATE TABLE STOCK([NO] INT,PROD_NAME VARCHAR(20),STOCK NUMERIC(19,2))
INSERT INTO STOCK
SELECT 1        ,'001',        150 UNION ALL
SELECT 2        ,'002',        50 SELECT T1.* 
,ISNULL(T2.STOCK,0)-(SELECT SUM(QTY) FROM [ORDER] T11 WHERE T11.PROD_NAME=T1.PROD_NAME
AND T11.SHIPDATE<=T1.SHIPDATE AND T11.[NO]<=T1.[NO]) '库存结余'
FROM [ORDER] T1
LEFT JOIN STOCK T2 ON T1.PROD_NAME=T2.PROD_NAME
/*
1 001 2009-02-27 00:00:00.000 100.00 50.00
2 001 2009-03-01 00:00:00.000 200.00 -150.00
3 001 2009-03-03 00:00:00.000 300.00 -450.00
4 002 2009-02-04 00:00:00.000 100.00 -50.00
5 002 2009-03-03 00:00:00.000 200.00 -250.00
6 003 2009-04-01 00:00:00.000 300.00 -300.00
7 004 2009-04-05 00:00:00.000 100.00 -100.00
*/

Answer 3

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-17 20:07:05
-------------------------------------
 
--> 生成测试数据: @Order
DECLARE @Order TABLE (No int,Prod_Name varchar(3),ShipDate datetime,Qty int)
INSERT INTO @Order
SELECT 1,'001','2009/02/27',100 UNION ALL
SELECT 2,'001','2009/03/01',200 UNION ALL
SELECT 3,'001','2009/03/03',300 UNION ALL
SELECT 4,'002','2009/02/04',100 UNION ALL
SELECT 5,'002','2009/03/03',200 UNION ALL
SELECT 6,'003','2009/04/01',300 UNION ALL
SELECT 7,'004','2009/04/05',100
 
--> 生成测试数据: @Stock
DECLARE @Stock TABLE (No int,Prod_Name varchar(3),Stock int)
INSERT INTO @Stock
SELECT 1,'001',150 UNION ALL
SELECT 2,'002',100 UNION ALL
SELECT 3,'002',50--SQL查询如下:SELECT
    A.*,
    Balance = ISNULL(B.Stock,0) 
                      - ISNULL((SELECT SUM(Qty) FROM @Order
                                WHERE Prod_Name = A.Prod_Name
                                      AND ShipDate <= A.ShipDate),0)
FROM @Order AS A
    LEFT JOIN (SELECT Prod_Name,SUM(Stock) AS Stock
          FROM @Stock GROUP BY Prod_Name) AS B
ON A.Prod_Name = B.Prod_Name/*
No          Prod_Name ShipDate                Qty         Balance
----------- --------- ----------------------- ----------- -----------
1           001       2009-02-27 00:00:00.000 100         50
2           001       2009-03-01 00:00:00.000 200         -150
3           001       2009-03-03 00:00:00.000 300         -450
4           002       2009-02-04 00:00:00.000 100         50
5           002       2009-03-03 00:00:00.000 200         -150
6           003       2009-04-01 00:00:00.000 300         -300
7           004       2009-04-05 00:00:00.000 100         -100(7 行受影响)
*/

Answer 4

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-17 20:07:05
-------------------------------------
 
--> 生成测试数据: @Order
DECLARE @Order TABLE (No int,Prod_Name varchar(3),ShipDate datetime,Qty int)
INSERT INTO @Order
SELECT 1,'001','2009/02/27',100 UNION ALL
SELECT 2,'001','2009/03/01',200 UNION ALL
SELECT 3,'001','2009/03/03',300 UNION ALL
SELECT 4,'002','2009/02/04',100 UNION ALL
SELECT 5,'002','2009/03/03',200 UNION ALL
SELECT 6,'003','2009/04/01',300 UNION ALL
SELECT 7,'004','2009/04/05',100
 
--> 生成测试数据: @Stock
DECLARE @Stock TABLE (No int,Prod_Name varchar(3),Stock int)
INSERT INTO @Stock
SELECT 1,'001',150 UNION ALL
SELECT 2,'002',100 UNION ALL
SELECT 3,'002',50--SQL查询如下:SELECT
    A.*,
    Balance =ISNULL(
                  CASE WHEN A.Qty < B.Stock
                      - ISNULL((SELECT SUM(Qty) FROM @Order
                                WHERE Prod_Name = A.Prod_Name AND ShipDate < A.ShipDate),0)
                       THEN A.Qty
                   ELSE
                       CASE WHEN B.Stock 
                                - ISNULL((SELECT SUM(Qty)
                                          FROM @Order
                                          WHERE Prod_Name = A.Prod_Name AND ShipDate < A.ShipDate),0) <= 0
                                THEN 0
                            ELSE
                                B.Stock 
                                - ISNULL((SELECT SUM(Qty)
                                          FROM @Order
                                          WHERE Prod_Name = A.Prod_Name AND ShipDate < A.ShipDate),0)
                        END
              END,0)
FROM @Order AS A
    LEFT JOIN (SELECT Prod_Name,SUM(Stock) AS Stock
          FROM @Stock GROUP BY Prod_Name) AS B
ON A.Prod_Name = B.Prod_Name
ORDER BY A.Prod_Name,A.ShipDate/*
No          Prod_Name ShipDate                Qty         Balance
----------- --------- ----------------------- ----------- -----------
1           001       2009-02-27 00:00:00.000 100         100
2           001       2009-03-01 00:00:00.000 200         50
3           001       2009-03-03 00:00:00.000 300         0
4           002       2009-02-04 00:00:00.000 100         100
5           002       2009-03-03 00:00:00.000 200         50
6           003       2009-04-01 00:00:00.000 300         0
7           004       2009-04-05 00:00:00.000 100         0(7 行受影响)*/

Answer 5

梁哥，这个语句中的
B.Stock 
                                - ISNULL((SELECT SUM(Qty)
                                          FROM @Order
                                          WHERE Prod_Name = A.Prod_Name AND ShipDate < A.ShipDate),0) <= 0在每行中只会计算一次吧？

Answer 6

IF OBJECT_ID('Order','U') IS NOT NULL DROP TABLE [Order] 
IF OBJECT_ID('Stock','U') IS NOT NULL DROP TABLE [Stock]
GO
CREATE TABLE [ORDER] (
[NO] INT,
PROD_NAME VARCHAR(20),
SHIPDATE DATETIME,
QTY NUMERIC(19,2)
)
INSERT INTO [ORDER]
SELECT 1        ,'001','2009/02/27',      100 UNION ALL
SELECT 2        ,'001','2009/03/01',      200 UNION ALL
SELECT 3        ,'001','2009/03/03',      300 UNION ALL
SELECT 4        ,'002','2009/02/04',      100 UNION ALL
SELECT 5        ,'002','2009/03/03',      200 UNION ALL
SELECT 6        ,'003','2009/04/01',      300 UNION ALL
SELECT 7        ,'004','2009/04/05',      100 
CREATE TABLE STOCK([NO] INT,PROD_NAME VARCHAR(20),STOCK NUMERIC(19,2))
INSERT INTO STOCK
SELECT 1        ,'001',        150 UNION ALL
SELECT 2        ,'002',        50 
 ;
 with cte1 as
(
select o.no,o.prod_Name,shipdate,qty,isnull(stock,0) stock from [Order] o 
left join Stock s on o.prod_name=s.prod_name
 )select *,库存结余=stock-dif from
(
select *,dif=(select sum(qty) from cte1 where SHIPDATE<=c.SHIPDATE and prod_name=c.prod_Name ) from cte1 c
) m/*
no          prod_Name            shipdate                qty                                     stock                                   dif                                     库存结余
----------- -------------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           001                  2009-02-27 00:00:00.000 100.00                                  150.00                                  100.00                                  50.00
2           001                  2009-03-01 00:00:00.000 200.00                                  150.00                                  300.00                                  -150.00
3           001                  2009-03-03 00:00:00.000 300.00                                  150.00                                  600.00                                  -450.00
4           002                  2009-02-04 00:00:00.000 100.00                                  50.00                                   100.00                                  -50.00
5           002                  2009-03-03 00:00:00.000 200.00                                  50.00                                   300.00                                  -250.00
6           003                  2009-04-01 00:00:00.000 300.00                                  0.00                                    300.00                                  -300.00
7           004                  2009-04-05 00:00:00.000 100.00                                  0.00                                    100.00                                  -100.00(7 行受影响)
*/