这个SQL怎么写?

[wjmsino]

有个基本表有物品种类A,数量B,和其它字段E,F,G
有个出库表:物品种类A,出库数量C,和其它字段
入库表:物品种类A,入库数量D,和其它字段
我要得到所有物品种类的数量=B + D - C和A,E,F等几个字段
基中出库表和入库表中不一定有所有物品种类

[fibbery]

select t1.种类,t1.b - t2.c + t3.d , t1.e , t1.f ....
from t1,t2,t3
where t1.种类=t2.种类 and t2.种类=t3.种类;

[fibbery]

用的是什么数据库,查一下,可能你需要在where中用到"连接".

[luotitan]

肯定要用左连接了，我先写一个试试。

[xiyahui]

同意楼上，用左连接写。呵呵看一下数据库的帮助有左连接的说明left join

[luotitan]

CREATE TABLE TB_GOODS
(F_ID Varchar(4),
F_NUM INT,
F_Name VarChar(20))


CREATE TABLE TB_STORAGEIN
(F_ID varchar(4),
F_NUM INT,
F_A VarChar(20))

CREATE TABLE TB_STORAGEOUT
(F_ID varchar(4),
F_NUM INT,
F_B VarChar(20))



INSERT INTO TB_GOODS(F_ID,F_NUM,F_Name)
VALUES('01',3,'货物1')
INSERT INTO TB_GOODS(F_ID,F_NUM,F_Name)
VALUES('02',4,'货物2')
INSERT INTO TB_GOODS(F_ID,F_NUM,F_Name)
VALUES('03',5,'货物3')


INSERT INTO TB_STORAGEIN(F_ID,F_NUM,F_A)
VALUES('01',2,'A')
INSERT INTO TB_STORAGEIN(F_ID,F_NUM,F_A)
VALUES('02',1,'C')


INSERT INTO TB_STORAGEOUT(F_ID,F_NUM,F_B)
VALUES('02',5,'E')
INSERT INTO TB_STORAGEOUT(F_ID,F_NUM,F_B)
VALUES('03',2,'F')

SELECT A.F_ID,A.F_NUM + ISNULL(B.F_NUM,0) - ISNULL(C.F_NUM,0) AS F_NUM,A.F_NAME,B.F_A,C.F_B
FROM TB_GOODS A
LEFT JOIN TB_STORAGEIN B
ON A.F_ID = B.F_ID
LEFT JOIN TB_STORAGEOUT C
ON A.F_ID = C.F_ID

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
可以参考一下

[wjmsino]

用的access数据库

[wjmsino]

to: luotitan(泰坦)

表TB_STORAGEIN,TB_STORAGEOUT两个表中的同一个物品种类有很多条记录，就只是入、出库单号不同
如TB_STORAGEIN中
VALUES('02',1,'C'，入库单1)
VALUES('02',1,'C'，入库单2)
这样该怎么样处理呢？

[luotitan]

没用过access，不是很清楚。

如果是SQL SERVER
SELECT A.F_ID,A.F_NUM + ISNULL(B.F_NUM,0) - ISNULL(C.F_NUM,0) AS F_NUM,A.F_NAME,B.F_A,C.F_B
FROM TB_GOODS A
LEFT JOIN (SELECT TB_STORAGEIN.F_ID as F_ID,SUM(TB_STORAGEIN.F_NUM) as F_NUM,TB_STORAGEIN.F_A as F_A
FROM TB_STORAGEIN
GROUP BY TB_STORAGEIN.F_ID,TB_STORAGEIN.F_A) as B
ON A.F_ID = B.F_ID
LEFT JOIN TB_STORAGEOUT C
ON A.F_ID = C.F_ID

[xl1004]

for oracle:
基本表-jbb;出库表-ckb;入库表-rkb

-----------------------------------------------------------------------------
select tab1.A,nvl(tab1.B,0) - nvl(tab2.C,0) + nvl(tab3.D,0),tab1.E,tab1.F,tab1.G from
jbb tab1,
(select A ,nvl(sum(C),0) as C from ckb group by A) tab2,
(select A ,nvl(sum(D),0) as D from rkb group by A) tab3
where tab1.A=tab2.A(+) and tab1.A = tab3.A(+);

[wjmsino]

哦，多加了B.F_A,和C.F_B这两个字段，搞定，结贴！
谢谢luotitan(泰坦)！

[wjmsino]

有没有FOR Access的语法？

[wjmsino]

Access 下怎么用啊？大侠们给点意见呀！

[wjmsino]

to: luotitan(泰坦) 还是不对：

01 5 货物1 A NULL
02 0 货物2 C E
02 0 货物2 e E
01 5 货物1 w NULL
03 3 货物3 NULL F

这个物品类别有重复且数量也不对，请看看

[wukejun]

up

[luotitan]

c和b的情况是一样的，难道你不看代码，copy就完事了？