--C单的数量如下select A.h_amount -sum(B.h_amount) as [数量] from B left join A on A.h_amount =B.h_amount
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[_temp_yw_px_item]( [px_id] [nvarchar](100) NULL, [h_id] [nvarchar](100) NULL, [h_amount] [nvarchar](100) NULL ) ON [PRIMARY] GO INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'A', N'123', N'100') INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'B', N'123', N'51') INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'C', N'123', N'49')--更新C单 update A set h_amount=B.h_amount*1-C.h_amount*1 FROM _temp_yw_px_item A LEFT JOIN _temp_yw_px_item B ON A.h_id=B.h_id AND B.px_id='A' LEFT JOIN _temp_yw_px_item C ON A.h_id=C.h_id AND C.px_id='B' WHERE A.px_id='C' 楼主看看 是不是这样,下次楼主问问题 最好给些数据,不然大家得猜。
UPDATE yw_px_item SET C.h_amount = A.h_amount - B.h_amount FROM yw_px_item C, (SELECT * FROM yw_px_item WHERE px_id='px01') AS A, (SELECT * FROM yw_px_item WHERE px_id='px02') AS B WHERE C.px_id = 'px03' AND C.h_id = A.hid AND C.h_id = B.hid
select isnull(a.h_amount,0)-isnull(b.h_amount,0) as 量 from yw_px_item a inner join yw_px_item b on a.px-id=A and b.px-id=b and a.h_id=b.h_id
就是按照8楼的要求写的,只不过没有调试而已 CREATE TABLE yw_px_item( px_id varchar(4), h_id varchar(6), h_amount int ) GOINSERT INTO yw_px_item VALUES('px01','00a001',3) INSERT INTO yw_px_item VALUES('px01','00a002',3) INSERT INTO yw_px_item VALUES('px02','00a001',1) INSERT INTO yw_px_item VALUES('px02','00a002',2)INSERT INTO yw_px_item SELECT 'px03', h_id, h_amount FROM yw_px_item WHERE px_id = 'px01'UPDATE yw_px_item SET h_amount = A.h_amount - B.h_amount FROM yw_px_item C, (SELECT * FROM yw_px_item WHERE px_id = 'px01') AS A, (SELECT * FROM yw_px_item WHERE px_id = 'px02') AS B WHERE C.px_id = 'px03' AND C.h_id = A.h_id AND C.h_id = B.h_id
A
on A.h_amount =B.h_amount
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[_temp_yw_px_item](
[px_id] [nvarchar](100) NULL,
[h_id] [nvarchar](100) NULL,
[h_amount] [nvarchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'A', N'123', N'100')
INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'B', N'123', N'51')
INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'C', N'123', N'49')--更新C单
update A
set h_amount=B.h_amount*1-C.h_amount*1
FROM _temp_yw_px_item A
LEFT JOIN _temp_yw_px_item B
ON A.h_id=B.h_id
AND B.px_id='A'
LEFT JOIN _temp_yw_px_item C
ON A.h_id=C.h_id
AND C.px_id='B'
WHERE A.px_id='C' 楼主看看 是不是这样,下次楼主问问题 最好给些数据,不然大家得猜。
INSERT [dbo].[_temp_yw_px_item] ([px_id], [h_id], [h_amount]) VALUES (N'C', N'123', N'100')
不好意思啊,几天没上网,这问题到现在也没解决,现在我再详细描述一下情况。我这里的A单B单C单,只是在图书管理软件里的概念,软件打开后,现货批销界面里,有一堆单子,我这里的A B C,分别指的是这些单的单号,保存在数据库中时,也就是数据库字段px_id。
问题是,现在A单B单C单在数据库中,全部是保存在一张表里的,也就是保存在yw_px_item这个表里。
所以我一看到阁下那句 Update A,我就知道可能是我没说清楚了,A不是表名,只是一个表中的字段px_id的值,更新表,这语句只能写成Update yw_px_item,问题是下面的条件怎么写,SET语句当中,是要更新所有px_id=C的记录项中的h_amount值。用大白话我应该这么讲,在yw_px_item这张表中,px_id=C中的所有项(数据库中用h_id来标识)的h_amount值,要等于px_id=A的这个记录集中的对应项的h_amount,减去px_id=B这个记录集中对应项的h_amount值。这种更新语句,不知道怎么写。
h_id h_name
00a001 示例书名1
00a002 示例书名2第二个表 yw_px_item,记录所有单据详细信息,示范数据如下:
px_id h_id h_amount
px01 00a001 3
px01 00a002 3
px02 00a001 1
px02 00a002 2
-----------------
px03 00a001 3
px03 00a002 3这里的px_id在软件界面中就代表了一张张的单子,然后就是详细信息,目前,px03这个单子我是复制px01的,里面物品、数量均相同,我的意思是,更新px_id=px03的所有数据项,使h_id=00a001的h_amount,等于3-1=2,h_id=00a002的h_amounto 3-2=1。
当然在实际情况当中一张单子里不会只有两个记录,我想问这样的更新语句怎么写。
SET C.h_amount = A.h_amount - B.h_amount
FROM yw_px_item C,
(SELECT * FROM yw_px_item WHERE px_id='px01') AS A,
(SELECT * FROM yw_px_item WHERE px_id='px02') AS B
WHERE C.px_id = 'px03'
AND C.h_id = A.hid
AND C.h_id = B.hid
isnull(a.h_amount,0)-isnull(b.h_amount,0) as 量
from yw_px_item a inner join yw_px_item b on a.px-id=A and b.px-id=b and a.h_id=b.h_id
CREATE TABLE yw_px_item(
px_id varchar(4),
h_id varchar(6),
h_amount int
)
GOINSERT INTO yw_px_item VALUES('px01','00a001',3)
INSERT INTO yw_px_item VALUES('px01','00a002',3)
INSERT INTO yw_px_item VALUES('px02','00a001',1)
INSERT INTO yw_px_item VALUES('px02','00a002',2)INSERT INTO yw_px_item
SELECT 'px03', h_id, h_amount
FROM yw_px_item
WHERE px_id = 'px01'UPDATE yw_px_item
SET h_amount = A.h_amount - B.h_amount
FROM yw_px_item C,
(SELECT * FROM yw_px_item WHERE px_id = 'px01') AS A,
(SELECT * FROM yw_px_item WHERE px_id = 'px02') AS B
WHERE C.px_id = 'px03'
AND C.h_id = A.h_id
AND C.h_id = B.h_id
SELECT *
FROM yw_px_item
px_id h_id h_amount
----- ------ -----------
px01 00a001 3
px01 00a002 3
px02 00a001 1
px02 00a002 2
px03 00a001 2
px03 00a002 1