小弟请教一个视图问题

[gitionyet]

进货表同名数量统计，减去销售同名数量统计，得出库存视图：
purchases
品名 数量
双喜硬经 100
双喜软经 111
双喜硬南 222

sales
品名 数量
双喜硬经 10
双喜软经 20

create view P_P
as select 品名,sum(数量)as 进货总量 from purchases group by 品名

create view S_S
as select 品名,sum(数量)as 销售总量 from sales group by 品名

create view P_S
as select 品名,进货总量-销售总量 as 库存 from purchases left outer join sales on purchases.品名=sales.品名

结果：
品名 库存
双喜硬经 90
双喜软经 91
双喜硬南 null

这里因为sales没有"双喜硬南"的销售纪录，库存视图显示null，应该怎么做才能显示222呢？

[开着拖拉机泡妞]

-isnull（sales.数量,0）

[开着拖拉机泡妞]

--> 测试数据：[purchases]
if object_id('[purchases]') is not null drop table [purchases]
create table [purchases]([品名] varchar(8),[数量] int)
insert [purchases]
select '双喜硬经',100 union all
select '双喜软经',111 union all
select '双喜硬南',222
--> 测试数据：[sales]
if object_id('[sales]') is not null drop table [sales]
create table [sales]([品名] varchar(8),[数量] int)
insert [sales]
select '双喜硬经',10 union all
select '双喜软经',20


select 
     a.品名,a.数量-ISNULL(b.数量,0) as 数量
from 
     [purchases] a
left join 
     [sales] b
on
     a.品名=b.品名
/*
品名	数量
--------------------
双喜硬经	90
双喜软经	91
双喜硬南	222
*/

[Felixzhaowenzhong]

create table purchases (name nvarchar(32),qty int)
insert purchases  
select '双喜硬经',100 union
select '双喜软经',111 union
select '双喜硬南',222

create table sales(name nvarchar(32),qty int)

insert sales
select '双喜硬经',10 union
select '双喜软经',20

select a.name ,(SUM(a.qty)-IsNULL(sum(b.qty),0)) as 库存 from purchases a left outer join sales b on a.name=b.name
group by a.name 
 /*
 name	库存
双喜软经	91
双喜硬经	90
双喜硬南	222
*/
 
 DROP TABLE purchases
  DROP TABLE sales