select 药品库.药品id,药品库.药品名称, (库存表.数量+入库表.数量-出库表.数量) AS 期末数量, (库存表.金额+入库表.金额-出库表.金额) AS 期末金额 from 药品库 LEFT JOIN 出库表 ON 药品库.药品id=出库表.药品id LEFT JOIN 入库表 ON 药品库.药品id=入库表.药品id LEFT JOIN 库存表 ON 药品库.药品id=库存表.药品id
调试欢乐多
from 库存表,
(select 入库表.药品ID,sum(入库表.数量) as 入库表数量 from 入库表 group by 入库表.药品ID),
(select 出库表.药品ID,sum(出库表.数量) as 出库表数量 from 出库表 group by 出库表.药品ID)
where 出库表.药品ID=入库表.药品ID and 入库表.药品ID=库存表.药品ID
[Items] 药品库
[StockIn] 入库表
[StockOut] 出库表
[Stock] 库存表
FROM [库存表] LEFT JOIN [出库表] ON [库存表].[药品ID] = [出库表].[药品ID]
LEFT JOIN [入库表] ON [库存表].[药品ID] = [入库表].[药品ID]
GROUP BY [出库表].[药品ID],[入库表].[药品ID]
FROM [库存表] LEFT JOIN [出库表] ON [库存表].[药品ID] = [出库表].[药品ID]
LEFT JOIN [入库表] ON [库存表].[药品ID] = [入库表].[药品ID]
GROUP BY [库存表].[药品ID]MAX([库存表].[数量]) ,MIN([库存表].[数量]) 都可以!
这样可能不对
left join会出来null值
结果会出来期末数量会是 null !!!
select 期末数量=case
when SUM([出库表].[数量]) is null and SUM([入库表].[数量]) is not null then MAX([库存表].[数量]) - SUM([出库表].[数量])
when ......
....................
AS 入库数量, ISNULL(T.出库数量, 0) AS 出库数量, S.数量 + ISNULL(T.入库数量, 0)
- ISNULL(T.出库数量, 0) AS 期末数量
FROM 药品库 M LEFT OUTER JOIN
库存表 S ON M.药品ID = S.药品ID INNER JOIN
(SELECT 药品ID, SUM(入库数量) AS 入库数量, SUM(出库数量) AS 出库数量
FROM (SELECT M.药品ID, R.数量 AS 入库数量, 0 AS 出库数量
FROM 入库表 R INNER JOIN
药品库 M ON R.药品ID = M.药品ID
UNION ALL
SELECT M.药品ID, 0 AS 入库数量, C.数量 AS 出库数量
FROM 出库表 C INNER JOIN
药品库 M ON C.药品ID = M.药品ID) H
GROUP BY 药品ID) T ON S.药品ID = T.药品ID
FROM [库存表] LEFT JOIN [出库表] ON [库存表].[药品ID] = [出库表].[药品ID]
LEFT JOIN [入库表] ON [库存表].[药品ID] = [入库表].[药品ID]
GROUP BY [库存表].[药品ID]
to RainBows(大毛) :
为保证唯一性,最好用自动数值型;如果非要字符型,在程序开始时取出最大值,并对以后的每个新增id进行记录并手工递增!
一方面,数字没有任何意义,不方面记忆
另一方面,写SQL语句中的where子句,要非常小心
如:"select * from YourTable where ID="+ mvarID,
若mvarID='12' --没问题
mvarID='' --有问题
mvarid='#$#' --有问题