SELECT a.id, 总数, c.物品名称 AS 名称, a.总数+c.数量-b.数量 AS 在库, c.数量 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 厂商
FROM InputDetail
GROUP BY [id], nz([所属类别],''), nz([型号],''), nz([封装],''), nz([厂商],'')
) AS d ON a.id=d.id;
你的InputDetail中所属类别 型号 封装 厂商字段有NULL值
SELECT DISTINCT (a.ID), InputDetail.名称, a.total, InputDetail.安全库存, InputDetail.优选, InputDetail.归属, InputDetail.试作号, InputDetail.型号, InputDetail.封装, InputDetail.厂商, InputDetail.说明, InputDetail.名称编号, InputDetail.所属类别, InputDetail.类别编号
FROM (SELECT InputDetail.ID,sum(数量) AS total FROM InputDetail GROUP BY InputDetail.ID)a,InputDetail where InputDetail.ID=a.ID
要实现这个功能是不是也要
left join InputDetail i on i.ID = a.ID
全部如下:
select a.ID,a.总数,c.物品名称 as 名称,a.总数+c.数量-b.数量 as 在库
from ((
(SELECT ID,sum(数量) as 总数 from inputDetail group by ID) a
left join BorrowTable b on a.id=b.借用物品编号
)
left join GivebackTable c on a.id=c.物品编号)
left join inputDetail i on a.ID = i.ID
select a.id,总数,c.物品名称 as 名称,a.总数+c.数量-b.数量 as 在库,c.数量 as 安全库存 from ((
SELECT id,sum(数量) as 总数 from inputDetail group by id) a
left join BorrowTable b on a.id=b.借用物品编号)
left join GivebackTable c on a.id=c.物品编号