关于使用WITH语句创建临时表进行查询问题

Question

WITH
Discount AS
(
SELECT
CheckItems.CheckProductsRID,
CheckItems.CheckStateID,
CheckItems.CheckItemRID,
CheckItems.CheckItemName,
CheckItems.SalePrice,
case when CheckItem.MarketPrice<>0 then CheckItems.SalePrice/CheckItem.MarketPrice else 0 end 折扣
FROM vCheckItemsAB CheckItems FULL OUTER JOIN CheckItem ON CheckItem.RID = CheckItems.CheckItemRID
)
SELECT
EnterpriseOrder.OrderNo 订单号,
CustomerReg.WorkNo 客户编号,
CustomerReg.Name 姓名,
SysUser.Name 销售员,
CheckItem.MarketPrice 市场价,
Discount.SalePrice 销售价,
Discount.折扣,
CheckState.Name 状态
FROM Discount
INNER JOIN vCheckProductsAB CheckProducts ON CheckProducts.RID = Discount.CheckProductsRID
INNER JOIN vCustomerRegAB CustomerReg ON CustomerReg.RID = CheckProducts.CustomerRegRID
INNER JOIN CheckState ON CheckState.ID = Discount.CheckStateID
INNER JOIN CheckItem ON CheckItem.RID = Discount.CheckItemRID
INNER JOIN DepartmentType ON DepartmentType.RID = CheckItem.DepartmentTypeRID
inner join vEnterpriseOrderAB EnterpriseOrder on EnterpriseOrder.RID = CustomerReg.EnterpriseOrderRID
left join SysUser on SysUser.RID = CheckProducts.SalesManRID

where Discount.折扣 =@折扣 or @折扣 is null
怎么才能使用where语句，以折扣作为查询，添加一个变量，但是要求不输入变量值，即NULL时，查询出所有的结果，现在我按照这个where语句查询，@折扣为NULL的时候查询的是折扣为0的结果。
求解答。

Answer 1

这玩意判断下空值就好了,你这个sql "@折扣为NULL" 的时候,并不是折扣为0 ,出现的其实是全部的数据,因为满足你的查询条件 "@折扣 is null"
常见的用nvl、ifnull、isnull、coalesce之类的函数处理下就行了，但不知道你用的什么数据库,所以下面这个case when 的方法更通用

where Discount.折扣 =case when @折扣 is null then Discount.折扣 else @折扣 end