求一算法sql

Question

2,3,5为什么是null,值不够 时就置null吗?

Answer 1

本质与此相同http://topic.csdn.net/u/20081028/14/842b135f-c004-40b1-b60d-625722bbf4b3.html只是具体的数据计算方式上略有区别。楼主只需说明数据如何处理即可。

Answer 2

2，3，5是减量，不需要计算。
n_Qty_Out是统计该批次上减掉了多少货物

Answer 3

--这样子吗?IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_ID(N'[dbo].[#test]') and OBJECTPROPERTY(ID, N'IsUserTABLE') = 1)
DROP TABLE [dbo].[#test]CREATE TABLE [dbo].[#test] ([id] [bigint] IDENTITY (1, 1) NOT NULL ,
[n_Qty] [decimal](18, 4) NULL ,
[n_Qty_Out] [decimal](18, 4) NULL 
) ON [PRIMARY]INSERT INTO #test( n_qty )
SELECT 20 UNION ALL
SELECT -2 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20  SELECT id,n_Qty,
       out_Qty=CASE WHEN n_Qty<=0
                      THEN NULL
                    ELSE 
                      CASE WHEN n_Qty>=(SELECT SUM(n_qty)
                                 FROM #test
                                 WHERE id<=a.id
                                   AND id>=(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC))
                              THEN (SELECT SUM(n_qty)
                                 FROM #test
                                 WHERE id<=a.id
                                   AND id>=(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC))
                             ELSE n_Qty
                       END
               END
FROM #test AS a/*
id                   n_Qty                                   out_Qty
-------------------- --------------------------------------- ---------------------------------------
1                    20.0000                                 20.0000
2                    -2.0000                                 NULL
3                    -10.0000                                NULL
4                    15.0000                                 15.0000
5                    -20.0000                                NULL(5 行受影响)
*/

Answer 4

--这样呢?IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_ID(N'[dbo].[#test]') and OBJECTPROPERTY(ID, N'IsUserTABLE') = 1)
DROP TABLE [dbo].[#test]CREATE TABLE [dbo].[#test] ([id] [bigint] IDENTITY (1, 1) NOT NULL ,
[n_Qty] [decimal](18, 4) NULL ,
[n_Qty_Out] [decimal](18, 4) NULL 
) ON [PRIMARY]INSERT INTO #test( n_qty )
SELECT 20 UNION ALL
SELECT -2 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20  SELECT id,n_Qty,
       out_Qty=CASE WHEN n_Qty<=0
                      THEN NULL
                    ELSE 
                      CASE WHEN n_Qty>=(SELECT SUM(n_qty)
                                 FROM #test
                                 WHERE id<=a.id
                                   AND id>(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC))
                              THEN ABS((SELECT SUM(n_qty)
                                    FROM #test
                                    WHERE id<a.id
                                     AND id>(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC)))
                             ELSE n_Qty
                       END
               END
FROM #test AS a/*
id                   n_Qty                                   out_Qty
-------------------- --------------------------------------- ---------------------------------------
1                    20.0000                                 20.0000
2                    -2.0000                                 NULL
3                    -10.0000                                NULL
4                    15.0000                                 12.0000
5                    -20.0000                                NULL(5 行受影响)
*/

Answer 5

这样没问题了吧?IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_ID(N'[dbo].[#test]') and OBJECTPROPERTY(ID, N'IsUserTABLE') = 1)
DROP TABLE [dbo].[#test]CREATE TABLE [dbo].[#test] ([id] [bigint] IDENTITY (1, 1) NOT NULL ,
[n_Qty] [decimal](18, 4) NULL ,
[n_Qty_Out] [decimal](18, 4) NULL 
) ON [PRIMARY]INSERT INTO #test( n_qty )
SELECT 20 UNION ALL
SELECT -10 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20  SELECT id,n_Qty,
       out_Qty=CASE WHEN n_Qty<=0
                      THEN NULL
                    ELSE 
                      CASE WHEN n_Qty>=ABS((SELECT SUM(n_qty)
                                 FROM #test
                                 WHERE id<=a.id
                                   AND id>=(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC)))
                              THEN ABS((SELECT SUM(n_qty)
                                    FROM #test
                                    WHERE id<=a.id
                                     AND id>=(SELECT TOP 1 id
                                            FROM #test
                                            WHERE id<a.id
                                              AND n_Qty>0
                                            ORDER BY id DESC)))
                             ELSE n_Qty
                       END
               END
FROM #test AS a/*
id                   n_Qty                                   out_Qty
-------------------- --------------------------------------- ---------------------------------------
1                    20.0000                                 20.0000
2                    -10.0000                                NULL
3                    -10.0000                                NULL
4                    15.0000                                 15.0000
5                    -20.0000                                NULL(5 行受影响)
*/

Answer 6

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_ID(N'[dbo].[#test]') and OBJECTPROPERTY(ID, N'IsUserTABLE') = 1)
DROP TABLE [dbo].[#test]CREATE TABLE [dbo].[#test] ([id] [bigint] IDENTITY (1, 1) NOT NULL ,
[n_Qty] [decimal](18, 4) NULL ,
[n_Qty_Out] [decimal](18, 4) NULL 
) ON [PRIMARY]INSERT INTO #test( n_qty )
SELECT 20 UNION ALL
SELECT -2 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20  declare @qty_out [decimal](18, 4)select @qty_out=SUM(n_Qty)
from #test
where n_Qty<0declare @tempqty [decimal](18, 4)
declare @tempqty1 [decimal](18, 4)update a set
  @tempqty=(select SUM(n_Qty) from #test where n_Qty>0 and id<= a.id),
  @tempqty1=isnull((select SUM(n_Qty) from #test where n_Qty>0 and id< a.id),0),
  [n_Qty_Out]=case when @tempqty+@qty_out<=0 then  a.n_Qty 
                   when @tempqty1+@qty_out<=0 then  -(@tempqty1+@qty_out)
                   else null
                   end
from [#test] a
where a.n_Qty >0
SELECT * from #test
goDROP TABLE #test--结果
id                   n_Qty                                   n_Qty_Out
-------------------- --------------------------------------- ---------------------------------------
1                    20.0000                                 20.0000
2                    -2.0000                                 NULL
3                    -10.0000                                NULL
4                    15.0000                                 12.0000
5                    -20.0000                                NULL(5 行受影响)

Answer 7

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_ID(N'[dbo].[#test]') and OBJECTPROPERTY(ID, N'IsUserTABLE') = 1)
DROP TABLE [dbo].[#test]CREATE TABLE [dbo].[#test] ([id] [bigint] IDENTITY (1, 1) NOT NULL ,
[n_Qty] [decimal](18, 4) NULL ,
[n_Qty_Out] [decimal](18, 4) NULL 
) ON [PRIMARY]INSERT INTO #test( n_qty )
SELECT 20 UNION ALL
SELECT -2 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20
--添多几条数据
UNION ALL
SELECT 10
UNION ALL
SELECT -5
UNION ALL
SELECT 10declare @qty_out [decimal](18, 4)select @qty_out=SUM(n_Qty)
from #test
where n_Qty<0declare @tempqty [decimal](18, 4)
declare @tempqty1 [decimal](18, 4)update a set
  @tempqty=(select SUM(n_Qty) from #test where n_Qty>0 and id<= a.id),
  @tempqty1=isnull((select SUM(n_Qty) from #test where n_Qty>0 and id< a.id),0),
  [n_Qty_Out]=case when @tempqty+@qty_out<=0 then  a.n_Qty 
                   when @tempqty1+@qty_out<=0 then  -(@tempqty1+@qty_out)
                   else null
                   end
from [#test] a
where a.n_Qty >0
SELECT * from #test
goDROP TABLE #test
--结果id                   n_Qty                                   n_Qty_Out
-------------------- --------------------------------------- ---------------------------------------
1                    20.0000                                 20.0000
2                    -2.0000                                 NULL
3                    -10.0000                                NULL
4                    15.0000                                 15.0000
5                    -20.0000                                NULL
6                    10.0000                                 2.0000
7                    -5.0000                                 NULL
8                    10.0000                                 NULL(8 行受影响)