内容摘要：left join中能把连接不到的空值设为0

ISNULL
使用指定的替换值替换 NULL。

语法
ISNULL ( check_expression , replacement_value ) 

 

select o.ID,o.Number,o.MaterialName,o.Unit,o.Note,o.Cost,c.CompanyName,O.Cost,isnull(s.CompanyName,'') as SupplyCompanyName ,o.Quantity-isnull(b.Number,0) as Quantity,isnull(b.Number,0) as SubNumber  from OrderDetail as o inner join Company as c on c.CompanyID=o.CompanyID  left join PurchaseDetail as p on  (p.OrderDetailID=o.ID and p.DateTime='20201201') left join SupplyCompany as s on  (p.CompanyID=s.CompanyID) left join OrderDetailSub as b on o.ID=b.OrderDetailID where o.DateTime='20201201' and o.CategoryID='9' order by o.MaterialName DESC