征求一条SQL语句

[vbkid]

我想用一条SQL语句实现从两张表里求出合计差异!
我有两张表,一个是入库表,字段是 存货编码,日期,数量,单价,金额
另一张是出库表,字段是 存货编码,日期,数量,单价,金额

现在我想得到一个统计结果,反映每种存货的进小计与出小计的差额!

[hjb111]

select sum(A.数量)-sum(B.数量) from 入库表 A,出库表 B where A.存货编码=B.存货编码
group by A.存货编码

[pengdali]

select 存货编码,sum(数量) 差额 from (
select 存货编码,数量 from 入库表
union all
select 存货编码,-数量 from 出库表) tem group by 存货编码

[vbkid]

例子表:

入库表:

t001 钢笔 2003-01-01 100 10 1000
t001 钢笔 2003-08-01 100 50 5000
t002 铅笔 2003-02-01 5 110 550
...

出库表

t001 钢笔 2003-05-01 120 40 4800
t002 铅笔 2003-08-10 6 30 180
t002 铅笔 2003-08-12 6 50 300

我想得到统计结果:

t001 钢笔 null null 20 1200
t002 铅笔 null null 20 70

[caiyunxia]

SELECT A.存货编码,RSL -ISNULL(CSL,0)
FROM (SELECT 存货编码,SUM(数量) RSL FROM 入库表 GROUP BY 存货编码) A LEFT JOIN
(SELECT 存货编码,SUM(数量) CSL FROM 出库表 GROUP BY 存货编码) B
ON A.存货编码=B.存货编码

[caiyunxia]

SELECT A.存货编码,RSL -ISNULL(CSL,0) ,RJE-ISNULL(CJE,0)
FROM (SELECT 存货编码,SUM(数量) RSL,SUM(金额) RJE FROM 入库表 GROUP BY 存货编码) A LEFT JOIN
(SELECT 存货编码,SUM(数量) CSL,SUM(金额) CJE FROM 出库表 GROUP BY 存货编码) B
ON A.存货编码=B.存货编码

[pengdali]

select 存货编码,存货名称,null,null,sum(数量) 数量,sum(金额) 差额 from (
select 存货编码,数量 from 入库表
union all
select 存货编码,-数量 from 出库表) tem group by 存货编码,存货名称

[mgan]

select 存货编码,名称,sum(数量) 差额 from (
select 存货编码,名称,数量 from 入库表
union all
select 存货编码,-数量 from 出库表) tem group by 存货编码

[vbkid]

大力兄,好像提示 FROM 字句语法错误呀!
FROM 后面能直接加子查询吗?

[pengdali]

select 存货编码,名称,sum(数量) 差额 from (
select 存货编码,名称,数量 from 入库表
union all
select 存货编码,名称,-数量 from 出库表) tem group by 存货编码

[pengdali]

FROM 后面能直接加子查询吗?

可以，关键是后面的表别名“tem”

[vbkid]

好像还不行!
我的数据库是ASA 6.0,我现在在ACCESS里测试!
我的句子是:
select dm,sum(realje) from (select dm,realje from xmskjd union all select dm,
-realje from xmfkjd) tem group by dm
提示:FROM字句语法错误!

[vbkid]

唉!大力兄,这好像是Transact-SQL的扩展功能!
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)

[skyyiyang]

select 存货编码,名称,sum(数量) 差额 from (
select 存货编码,名称,数量 from 入库表
union all
select 存货编码,名称,-数量 from 出库表) tem group by 存货编码

[vbkid]

很抱歉,前几天不能上网,不能及时结帖!