送分给wwwb,--------请跟帖接分

[corder_li]

还有个问题就是

查询是对的:SELECT a.ID, c.物品名称 AS 名称, a.总数, a.总数+c.数量-b.数量 AS 在库, [安全库存],[优选],[试作号],[型号], [封装], [厂商],[说明],[名称编号],[所属类别],[类别编号]
FROM
(
(
(SELECT ID,sum(数量) as 总数 from inputDetail group by id) AS a
LEFT JOIN BorrowTable AS b ON a.id=b.借用物品编号
)
LEFT JOIN GivebackTable AS c ON a.id=c.物品编号
)
LEFT JOIN
( SELECT [ID],nz([InputDetail.安全库存],'') as 安全库存,[优选],[归属],nz([InputDetail.试作号],'') as 试作号,nz([InputDetail.型号],'') as 型号, nz([InputDetail.封装],'') as 封装,nz([InputDetail.厂商],'') as 厂商,
nz([InputDetail.说明],'')as 说明,[名称编号],[所属类别],[类别编号] FROM InputDetail GROUP BY [ID],nz([安全库存],'') ,[优选],[归属],nz([试作号],'') ,nz([InputDetail.型号],'') , nz([InputDetail.封装],''),nz([InputDetail.厂商],'') ,
nz([说明],'') ,[名称编号],[所属类别],[类别编号]
)
AS d ON a.id=d.id


而插入操作有问题:查询的数目和目标字段 不同

insert into GoodsInfo(ID,名称,总数,在库,安全库存,优选,归属,试作号,型号,封装,厂商,说明,名称编号,所属类别,类别编号)
select e.* from
(SELECT a.ID, c.物品名称 AS 名称, a.总数,nz(a.总数,0)+nz(c.数量,0)-nz(b.数量,0) AS 在库, [安全库存],[优选],[归属],[试作号],[型号], [封装], [厂商],[说明],
[名称编号],[所属类别],[类别编号]
FROM
(
(
(SELECT ID,sum(数量) as 总数 from inputDetail group by id) AS a
LEFT JOIN BorrowTable AS b ON a.id=b.借用物品编号
)
LEFT JOIN GivebackTable AS c ON a.id=c.物品编号
)
LEFT JOIN
( SELECT [ID],nz([InputDetail.安全库存],'') as 安全库存,[优选],[归属],nz([InputDetail.试作号],'') as 试作号,nz([InputDetail.型号],'') as 型号, nz([InputDetail.封装],'') as 封装,nz([InputDetail.厂商],'') as 厂商,
nz([InputDetail.说明],'')as 说明,[名称编号],[所属类别],[类别编号] FROM InputDetail GROUP BY [ID],nz([安全库存],'') ,[优选],[归属],nz([试作号],'') ,nz([InputDetail.型号],'') , nz([InputDetail.封装],''),nz([InputDetail.厂商],'') ,
nz([说明],'') ,[名称编号],[所属类别],[类别编号]
)
AS d ON a.id=d.id)e
left join GoodsInfo f on f.ID = e.ID where isnull(f.ID)

[wwwwb]

insert into GoodsInfo
select e.* from (
SELECT a.ID, c.物品名称 AS 名称, a.总数,nz(a.总数,0)+nz(c.数量,0)-nz(b.数量,0) AS 在库, [安全库存],[优选],[归属],[试作号],[型号], [封装], [厂商],[说明], [名称编号],[所属类别],[类别编号] FROM ( ( (SELECT ID,sum(数量) as 总数 from inputDetail group by id) AS a LEFT JOIN BorrowTable AS b ON a.id=b.借用物品编号 ) LEFT JOIN GivebackTable AS c ON a.id=c.物品编号 ) LEFT JOIN ( SELECT [ID],nz([InputDetail.安全库存],'') as 安全库存,[优选],[归属],nz([InputDetail.试作号],'') as 试作号,nz([InputDetail.型号],'') as 型号, nz([InputDetail.封装],'') as 封装,nz([InputDetail.厂商],'') as 厂商, nz([InputDetail.说明],'')as 说明,[名称编号],[所属类别],[类别编号] FROM InputDetail GROUP BY [ID],nz([安全库存],'') ,[优选],[归属],nz([试作号],'') ,nz([InputDetail.型号],'') , nz([InputDetail.封装],''),nz([InputDetail.厂商],'') , nz([说明],'') ,[名称编号],[所属类别],[类别编号] ) AS d ON a.id=d.id) e
left join GoodsInfo f on f.ID = e.ID where isnull(f.ID)

不知道为什么加了字段名有问题，我在测试一下

[wwwwb]

insert into GoodsInfo
([ID],[名称],[总数],[在库],[安全库存],[优选],归属,试作号,型号,封装,厂商,说明,名称编号,所属类别,类别编号)
SELECT [ID],[名称],[总数],[在库],qq.[安全库存],qq.[优选],qq.归属,qq.试作号,qq.型号,qq.封装,qq.厂商,
qq.说明,qq.名称编号,qq.所属类别,qq.类别编号
FROM
(SELECT [a.ID] AS id, c.物品名称 AS 名称, [a.总数] AS 总数, nz(a.总数,0)+nz(c.数量,0)-nz(b.数量,0) AS 在库, [安全库存], [优选], [归属], [试作号], [型号], [封装], [厂商], [说明], [名称编号], [所属类别], [类别编号]
FROM (((SELECT ID,sum(数量) as 总数 from inputDetail group by id) AS a LEFT JOIN BorrowTable AS b ON a.id=b.借用物品编号) LEFT JOIN GivebackTable AS c ON a.id=c.物品编号) LEFT JOIN (SELECT [ID],nz([InputDetail.安全库存],'') as 安全库存,[优选],[归属],nz([InputDetail.试作号],'') as 试作号,nz([InputDetail.型号],'') as 型号, nz([InputDetail.封装],'') as 封装,nz([InputDetail.厂商],'') as 厂商, nz([InputDetail.说明],'') as 说明,[名称编号],[所属类别],[类别编号] FROM InputDetail GROUP BY [ID],nz([安全库存],'') ,[优选],[归属],nz([试作号],'') ,nz([InputDetail.型号],'') , nz([InputDetail.封装],''),nz([InputDetail.厂商],'') , nz([说明],'') ,[名称编号],[所属类别],[类别编号]) AS d ON a.id=d.id
)
AS qq LEFT JOIN GoodsInfo AS ff ON ff.ID = qq.ID
WHERE isnull(ff.ID);