我没有写触发器,只写了实现方法,实际上以下代码是套在触发器里用的。我的测试数据下,tb是空表,那么我在向它一次性插入三条记录后,用它里面的数据来处理ta. 这时 tb 中的数据就可以理解为 inserted (因为一开始它是空的,那么现在表里的数据,就等同于插入操作时inserted里的数据 )由于没有标识列(或唯一列),(当然,如果单号唯一且可排序的话,就不用临时表,直接拿单号就可以了。)所以我用了临时表。将语义逻辑改到触发器里执行即可。当然,sql2005下也可以不用触发器,利用OUTPUT指令得到inserted里的数据到表变量再操作,也是可以的。我就不写了(重在逻辑,语法不再累赘)。IF object_id('ta','u') IS NOT NULL DROP TABLE ta IF object_id('tb','u') IS NOT NULL DROP TABLE tb GOCREATE TABLE ta(bookID VARCHAR(2),goodsID VARCHAR,bookNUM INT,postNUM INT) CREATE TABLE tb(bookID VARCHAR(2),goodsID VARCHAR,NUM INT) GOINSERT ta SELECT 'A1' , 'A', 100 , 0 UNION ALL SELECT 'A2', 'A' , 200, 0 UNION ALL SELECT 'A3', 'A' , 200, 0 UNION ALL SELECT 'B1', 'B', 150, 20GOINSERT tb SELECT 'X1', 'A', 190 UNION ALL SELECT 'Y1', 'B', 100 GO --SELECT * FROM ta --SELECT * FROM tbSELECT IDENTITY(INT) ID ,* INTO #1 FROM ta SELECT IDENTITY(INT) ID, * INTO #2 FROM tb UPDATE c SET c.postNUM= CASE WHEN a.nNum<=num THEN CASE WHEN a.bookNUM+nNum>=num THEN num - nNum ELSE a.bookNUM END END + a.postNUM FROM #1 c INNER JOIN (SELECT *,nNum=ISNULL((SELECT SUM(bookNUM-postNUM) FROM #1 WHERE goodsID=a.goodsID AND ID<a.ID),0) FROM #1 a) a ON c.id=a.id INNER JOIN (SELECT goodsID,SUM(num) num FROM #2 a GROUP BY goodsID) b ON a.goodsID=b.goodsIDSELECT * FROM #1 /* 1 A1 A 100 100 2 A2 A 200 90 3 A3 A 200 NULL 4 B1 B 150 120*/ GODROP TABLE #1,#2 GO
CREATE TABLE #A (OrderNo VARCHAR(20),Part VARCHAR(30),Qty INT,ExQty INT) INSERT INTO #A SELECT 'A1','A',100,0 UNION ALL SELECT 'A2','A',200,0CREATE TABLE #B (SaleNo VARCHAR(20),Part VARCHAR(30),SaleQty INT) INSERT INTO #B VALUES ('X1','A',190)CREATE CLUSTERED INDEX IXC_PK ON #A(OrderNo,Part)DECLARE @x INT,@y int,@Part varchar(30) UPDATE #A SET @X=CASE WHEN ISNULL(@Part,'')<>Part THEN (SELECT SUM(SaleQty) FROM #B WHERE Part=#A.Part )ELSE @X END, @Part=CASE WHEN @Part=Part THEN @Part ELSE Part END, @y=@x, ExQty=CASE WHEN @y-Qty >0 THEN Qty ELSE CASE WHEN @y>0 THEN @y ELSE 0 END END, @x=@x-Qty SELECT * from #A DROP TABLE #A,#B
To fcuandy,Garnett_KG 太棒了,虽然还没看懂,但测试了一下都能做到.感谢,又学会了一种方法. 谁能帮我解释一下吗. 如果我在B表里面录入了负的数量,怎么冲减A表里面的已交数呢.这表要怎么写呢. 谢谢了
DROP TABLE ta
IF object_id('tb','u') IS NOT NULL
DROP TABLE tb
GOCREATE TABLE ta(bookID VARCHAR(2),goodsID VARCHAR,bookNUM INT,postNUM INT)
CREATE TABLE tb(bookID VARCHAR(2),goodsID VARCHAR,NUM INT)
GOINSERT ta SELECT 'A1' , 'A', 100 , 0
UNION ALL SELECT 'A2', 'A' , 200, 0
UNION ALL SELECT 'A3', 'A' , 200, 0
UNION ALL SELECT 'B1', 'B', 150, 20GOINSERT tb SELECT 'X1', 'A', 190
UNION ALL SELECT 'Y1', 'B', 100
GO
--SELECT * FROM ta
--SELECT * FROM tbSELECT IDENTITY(INT) ID ,* INTO #1 FROM ta
SELECT IDENTITY(INT) ID, * INTO #2 FROM tb
UPDATE c SET c.postNUM=
CASE WHEN a.nNum<=num THEN
CASE WHEN a.bookNUM+nNum>=num THEN
num - nNum
ELSE a.bookNUM
END
END + a.postNUM
FROM #1 c
INNER JOIN
(SELECT *,nNum=ISNULL((SELECT SUM(bookNUM-postNUM) FROM #1 WHERE goodsID=a.goodsID AND ID<a.ID),0) FROM #1 a) a
ON c.id=a.id
INNER JOIN
(SELECT goodsID,SUM(num) num FROM #2 a GROUP BY goodsID) b
ON a.goodsID=b.goodsIDSELECT * FROM #1
/*
1 A1 A 100 100
2 A2 A 200 90
3 A3 A 200 NULL
4 B1 B 150 120*/
GODROP TABLE #1,#2
GO
INSERT INTO #A
SELECT 'A1','A',100,0
UNION ALL
SELECT 'A2','A',200,0CREATE TABLE #B (SaleNo VARCHAR(20),Part VARCHAR(30),SaleQty INT)
INSERT INTO #B VALUES ('X1','A',190)CREATE CLUSTERED INDEX IXC_PK ON #A(OrderNo,Part)DECLARE @x INT,@y int,@Part varchar(30)
UPDATE #A
SET @X=CASE WHEN ISNULL(@Part,'')<>Part THEN (SELECT SUM(SaleQty) FROM #B WHERE Part=#A.Part )ELSE @X END,
@Part=CASE WHEN @Part=Part THEN @Part ELSE Part END,
@y=@x,
ExQty=CASE WHEN @y-Qty >0 THEN Qty ELSE CASE WHEN @y>0 THEN @y ELSE 0 END END,
@x=@x-Qty
SELECT * from #A
DROP TABLE #A,#B