实现效果：
create view [dbo].[v_show_stockinorderitem]
as
with cte_detail as
(
--已入库数量
select s.productid,s.colorsid,s.sizesid,s.title,s.colorsname,s.sizesname,s.quantity as qty
from thebeerhouse.stockinitems s
left join thebeerhouse.stockin si on si.stockinid = s.stockinid
where si.status='audited' --已审核的标志，
--thebeerhouse.stockin相当于是一个订单，谁什么时间入库
--thebeerhouse.stockinitems 与订单表相关联，具体这个订单入库的哪个产品，数量，颜色，尺码等
union all
--已出库数量
select o.productid as oproductid,o.colorsid as ocolorsid,o.sizesid as osizesid,o.title as otitle,
o.colorsname as ocolorsname,o.sizesname as osizesname,-o.quantity as qty
from thebeerhouse.orderitems o
left join thebeerhouse.orders oo on oo.orderid = o.orderidwhere oo.shippingstatus = 'applyshipped' --已出库的标志
--thebeerhouse.orders 出库订单，谁什么时间出库
--thebeerhouse.orderitems 与订单表相关联，具体这个订单出库的哪个产品，数量，颜色，尺码等
)
--把上边的结果都结合起来
,cte_qty as
(
select productid,sum(qty) as qty,title,sizesid,sizesname,colorsid,colorsname --qty为入库量-出库量也就是库存余量
from cte_detail
where productid=productid and colorsid=colorsid and sizesid=sizesid
group by productid,title,sizesid,sizesname,colorsid,colorsname
)
select row_number() over(order by t.productid) as '数据标识'
,产品标识 = t.productid
,row_number() over(order by t.productid) as '序号'
,产品编号 = t.productid
,产品名称 = t.title
,颜色=t.colorsname
,尺码=t.sizesname
,入库总量=(select isnull(sum(s.quantity),0)
from thebeerhouse.stockinitems s
left join thebeerhouse.stockin si on si.stockinid = s.stockinid
where si.status='audited' and s.productid=t.productid and s.sizesid=t.sizesid and s.colorsid=t.colorsid)
,销售总量=(select isnull(sum(o.quantity),0)
from thebeerhouse.orderitems o
left join thebeerhouse.orders oo on oo.orderid = o.orderid
where oo.shippingstatus = 'applyshipped' and o.productid=t.productid and o.sizesid=t.sizesid and o.colorsid=t.colorsid)
,库存数量 = isnull(t.qty,0)
from
cte_qty as t
go