22,209
社区成员
发帖
与我相关
我的任务
分享
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 * from #test
go
DROP TABLE #test
(所影响的行数为 5 行)
id n_Qty n_Qty_Out
-------------------- -------------------- --------------------
1 20.0000 NULL
2 -2.0000 NULL
3 -10.0000 NULL
4 15.0000 NULL
5 -20.0000 NULL
(所影响的行数为 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 -2 UNION ALL
SELECT -10 UNION ALL
SELECT 15 UNION ALL
SELECT -20
--添多几条数据
UNION ALL
SELECT 10
UNION ALL
SELECT -5
UNION ALL
SELECT 10
declare @qty_out [decimal](18, 4)
select @qty_out=SUM(n_Qty)
from #test
where n_Qty<0
declare @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
go
DROP 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 行受影响)
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<0
declare @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
go
DROP 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 行受影响)
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 行受影响)
*/
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 行受影响)
*/
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 行受影响)
*/