急！商品出库和入库问题。分全给你。在线等待

[cb42239]

数据库有两个表分别是商品入库明细表和商品出库明细表，其结构为:
itmein_tbl(商品入库明细表)：
shopid(商店编号)
itemid(商品编号）
itemsnum（商品入库数量）
providerid(供货商编号)
indatetime(商品入库时间）

itemout_tbl(商品出库明细表)：
shopid(商店编号)
itemid(商品编号）
itemsnum（商品出库数量）
receiverid(要货商编号)
outdatetime(商品出库时间）

我已经从这两个表得到了两个视图分别为：商品入库统计表(按商店编号，商品编号，入库时间group by)和商品出库统计表(按商店编号，商品编号，出库时间group by)。
现在，我想实现的是将商店编号、商品编号、商品入库数量、商品出库数量、时间
这些字段显示在同一张表中，也就是说客户能够看到某一天的具体情况。如果某一天
某一商店的某种商品只有入库而没有出库，其相对应的出库为0。如果只有出库而没有入库，也是一样。
我想了很久，也没有实现，我觉得好像要完成不可能。那位高手能够解决呀。

[cyberwizard]

扬帆破浪的方法不错!!!

[caiyunxia]

select shopid,itemid,sum(insl),sum(outsl), sj from (
select shopid,itemid,itemsnum as insl,0 as outsl,indatetime sj from itmein_tbl
union
select shopid,itemid, 0 as insl,itemsnum as outsl ,outdatetime sj from itemout_tbl ) b
group by shopid,itemid,sj

[j9988]

caiyunxia(monkey)的好，不过SJ可能要处理一下

[j9988]

select
a.shopid,
a.itemid,
sum(a.itemsnum) as 商品入库数量,
sum(b.itemsnum) 商品出库数量,
convert(varchar(10),indatetime,120)
from 商品入库明细表 a,商品出库明细表 b
where a.shopid=b.shopid and a.itemid=b.itemid
group by convert(varchar(10),indatetime,120)

[j9988]

select
a.shopid,
a.itemid,
sum(a.itemsnum) as 商品入库数量,
sum(b.itemsnum) 商品出库数量,
convert(varchar(10),indatetime,120)
from 商品入库明细表 a,商品出库明细表 b
where a.shopid=b.shopid and a.itemid=b.itemid
group by a.shopid,a.itemid,convert(varchar(10),a.indatetime,120)

[saucer]

use LEFT OUTER JOIN, something like (did not test, so there might be oversight)

select t1.shopid, t1.itemid, t1.itemsnum as 商品入库数量, ISNULL(t2.itemsnum,0) as 商品出库数量, indatetime as 时间
from
(select shopid, itemid, indatetime, sum(itemsnum) as itemsnum from itmein_tbl group by shopid，itemid，indatetime) as t1
LEFT OUTER JOIN
(select shopid, itemid, outdatetime, sum(itemsnum) as itemsnum from itemout_tbl group by shopid，itemid，outdatetime) as t2
ON t1.shopid = t2.shopid
and t1.itemid = t2.itemid and t1.indatetime = t2.outdatetime

[cb42239]

to j9988(j9988)
你可能没有理解我的意思，我是要把每一天的情况（只要某一商店的某一
商品有出库或入库记录）都统计出来。你的方法不能完成。如果入库表有某一
商品在某一天aaa商店有入库记录，但是这一天aaa商店没有这一商店的出库
记录的话，那么就不能选出。
看看我的条件：如果某一天某一商店的某种商品只有入库而没有出库，其相对应的出库为0。如果只有出库而没有入库，也是一样。

[cb42239]

to saucer(思归)
如果用left outer join，将不能完成：如果入库表有某一商品在某一天aaa商店有出库记录，但是这一天aaa商店没有这一商店的入库记录的话，那么就不能选出。而且有用left outer join选出来有很多多余的记录

[Yang_]

1、你的“商品入库统计表(按商店编号，商品编号，入库时间group by)和商品出库统计表(按商店编号，商品编号，出库时间group by)。”是不是写错了，应该还有入库数量和出库数量！
2、如果1成立
select isnull(a.商店编号,b.商店编号) as 商店编号,
isnull(a.商品编号,b.商品编号) as 商品编号,
isnull(a.入库数量,0) as 商品入库数量,
isnull(b.出库数量,0) as 商品出库数量,
isnull(a.入库时间,b.出库时间) as 时间
from 商品入库统计表 a full join 商品出库统计表 b
on a.商店编号=b.商店编号
and a.商品编号=b..商品编号
and a.入库时间=b.出库时间

[saucer]

use FULL OUTER JOIN then, :-)

something like

select ISNULL(t1.shopid, t2.shopid) as 商店编号, ISNULL(t1.itemid, t2.itemid) as 商品编号, ISNULL(t1.itemsnum,0) as 商品入库数量, ISNULL(t2.itemsnum,0) as 商品出库数量, ISNULL(t1.indatetime, t2.outdatetime) as 时间
from
(select shopid, itemid, indatetime, sum(itemsnum) as itemsnum from itmein_tbl group by shopid，itemid，indatetime) as t1
LEFT OUTER JOIN
(select shopid, itemid, outdatetime, sum(itemsnum) as itemsnum from itemout_tbl group by shopid，itemid，outdatetime) as t2
ON t1.shopid = t2.shopid
and t1.itemid = t2.itemid and t1.indatetime = t2.outdatetime

[saucer]

sorry

LEFT OUTER JOIN
===>
FULL OUTER JOIN

[j9988]

select
a.shopid,
a.itemid,
sum(a.itemsnum) as 商品入库数量,
sum(b.itemsnum) 商品出库数量,
convert(varchar(10),c.indatetime,120)
from (
select convert(varchar(120),indatetime,120) as dd from 商品入库明细表
union
select convert(varchar(120),indatetime,120) as dd from 商品出库明细表
) as c
left join 商品入库明细表 a on x.dd=convert(varchar(120),a.indatetime,120)
left join 商品出库明细表 b on x.dd=convert(varchar(120),a.indatetime,120)
and a.shopid=b.shopid and a.itemid=b.itemid
group by a.shopid,a.itemid,convert(varchar(10),c.indatetime,120)

[j9988]

select
a.shopid,
a.itemid,
sum(a.itemsnum) as 商品入库数量,
sum(b.itemsnum) 商品出库数量,
c.dd,
from (
select convert(varchar(120),indatetime,120) as dd from 商品入库明细表
union
select convert(varchar(120),indatetime,120) as dd from 商品出库明细表
) as c
left join 商品入库明细表 a on x.dd=convert(varchar(120),a.indatetime,120)
left join 商品出库明细表 b on x.dd=convert(varchar(120),a.indatetime,120)
and a.shopid=b.shopid and a.itemid=b.itemid
group by a.shopid,a.itemid,c.dd

[Yang_]

也可以从明细表查：

select isnull(a.shopid,b.shopid) as 商店编号,
isnull(a.itemid,b.itemid) as 商品编号,
isnull(a.Qty,0) as 商品入库数量,
isnull(b.Qty,0) as 商品出库数量,
isnull(a.thedate,b.thedate) as 时间
from
(
select
shopid,
itemid,
sum(itemsnum) as Qty,
convert(varchar(8),indatetime,112)
from 商品入库明细表
group by
shopid,
itemid,
convert(varchar(8),indatetime,112) as thedate
) as a
full join
(
select
shopid,
itemid,
sum(itemsnum) as Qty,
convert(varchar(8),indatetime,112) as thedate
from 商品出库明细表
group by
shopid,
itemid,
convert(varchar(8),indatetime,112)
) as b
on
a.shopid=b.shopid
and itemid=b.itemid
and a.thedate=b.thedate

[zset]

试一下用临时表来实现