求教创建视图的问题 left join on

[weixin_41411165]

有几张表格,数据结构和测试数据内容如下
第一张表格:EP_location,仓库分类表 结构和数据如下


第二张表格:EP_commodity,成品名称表 结构和数据如下


第三张表格:EP_commodity_classify,成品品质表 结构和数据如下


第四张表格:EP_commodity_series,成品系列表 结构和数据如下


第五张表格:EP_commodity_category,成品类别表 结构和数据如下


第七张表格:EP_commodity_spec,成品规格表 结构和数据如下


第八张表格:EP_commodity_color,成品颜色表 结构和数据如下


第九张表格:EP_commodity_entry,成品入库表 结构和数据如下


第十张表格:EP_commodity_entry,成品出库表 结构和数据如下


现在我要创建一个视图表,方便查询
结构如下
commodityid,name,classify,series,cateory,spec,color,quantity,location
成品ID,成品名称,品质,系列,类别,规格,颜色,库存数量,仓库未知
创建view的SQL如下

IF OBJECT_ID('dbo.EP_commodity_search') IS NOT NULL
	DROP view dbo.EP_commodity_search
GO
CREATE VIEW [dbo].[EP_commodity_search] AS
select commodity.id as commodityid, commodity.name, classify.name as classify, series.name as series, category.name as category, spec.name as spec, color.name as color,
quantity = isnull(entry.quantity,0) - isnull(appear.quantity,0),
location = isnull(location.name,null)
from
(select id,name,classifyid,seriesid,categoryid,specid,colorid from EP_commodity)commodity
left join (select id,name from EP_commodity_classify)classify on classify.id = commodity.classifyid
left join (select id,name from EP_commodity_series)series on series.id = commodity.seriesid
left join (select id,name from EP_commodity_category)category on category.id = commodity.specid
left join (select id,name from EP_commodity_spec)spec on spec.id = commodity.specid
left join (select id,parentid,name from EP_commodity_color)color on color.id = commodity.colorid
left join (select commodityid,locationid,sum(quantity) as quantity from EP_commodity_entry group by commodityid,locationid)entry on entry.commodityid = commodity.id
left join (select commodityid,locationid,sum(quantity) as quantity from EP_commodity_appear group by commodityid,locationid)appear on appear.commodityid = commodity.id
left join (select commodityid,locationid from EP_commodity_location)commodityLocation on commodityLocation.commodityid = commodity.id
left join (select id,parentid,name from EP_location)location on location.id = commodityLocation.locationid
where isnull(entry.quantity,0) - isnull(appear.quantity,0) > 0
GO

数据显示如下,和预期的结果不一样.,明明只有一个产品,分别放入了两个仓库,这里有四条数据,而且数量也不对

请高手指教,最后创建视图应该怎么修改
坐等 谢谢~

[RINK_1]

估计是EP_commodity_location这张表的数据造成的。这张表是不是记录了入库和出库数据中货物和仓库之间的关系。是不是存在4条记录（2条入，2条出）。如果是的，那应该这张表根据出入库区分后，再分别和入库表或者出库表关联。 如果真如上述说的，那你这张表是不是有些多余，因为入库表和出库表中本来就记录了locationid，体现了每次入库或者出库的货物与仓库之间的关系，又何必再通过EP_commodity_location来体现这层关系呢。

[道素]

你的入库和出库怎么是一个表名 是不是出库和入库个两条，然后迪尔卡集了 不确定我理解的对不对，试试下面的逻辑（其实就是分别先根据产品的location汇总数量，然后在计算差值，然后再和其他表进行Join（减少干扰）

select id,name,classifyid,seriesid,categoryid,specid,colorid 
FROM EP_commodity AS commodity
LEFT JOIN EP_commodity_classify AS classify ON classify.id = commodity.classifyid
LEFT JOIN EP_commodity_series AS series on series.id = commodity.seriesid
LEFT JOIN EP_commodity_category AS category on category.id = commodity.specid
LEFT JOIN EP_commodity_spec AS spec on spec.id = commodity.specid
LEFT JOIN EP_commodity_color AS color on color.id = commodity.colorid
LEFT JOIN (
   SELECT entry.commodityid,entry.locationid,entry.quantity-isnull(appear,quantity,0) AS quantity FROM (
       SELECT commodityid,locationid,sum(quantity) as quantity from EP_commodity_entry group by commodityid,locationid
    ) AS entry
    LEFT JOIN (
        SELECT commodityid,locationid,sum(quantity) as quantity from EP_commodity_appear group by commodityid,locationid
        ) AS appear  on appear.commodityid = entry.commodityid

) AS q
left join  EP_location AS location on location.id = q.locationid
WHERE q.quantity>0

[weixin_41411165]

问题已经解决. EP_commodity_entry作为最左侧的表搞定,谢谢各位的回答~~