SELECT '' AS ProductId,
'' AS Name,
'' AS billsheetno,
'' AS BillSheetId,
nullif('',0) AS BuyInPrice,
'' AS xsj,
'' AS createdon,
t5.ClassId,
t5.ClassNumber+'(小计)' AS ClassNumber,
'' AS MeasurementUnit,--单位
'' AS gysname,
'' AS sku,--商品编号
SUM(t3.Quantity) AS Quantity,
SUM(nullif(t3.Quantity,0)*nullif(t3.BuyInPrice,0)) AS amount ,
SUM(nullif(t3.quantity,0)*nullif(t3.costprice,0)) as cbje
FROM dbo.Product t1 left join dbo.Inventory_Consignor t4 on t1.SupplierId=t4.ConsignorId,
dbo.Inventory_BillSheet t2 ,
dbo.Inventory_BillSheet_Item t3 ,
dbo.User_Class t5
WHERE t1.ProductId=t3.ProductId
AND t2.BillSheetId=t3.BillSheetId and t2.billtype=2 and t2.orderstatusid=7
AND t5.ClassId=t2.UserClassId
AND t1.isDurability=1
AND t2.CreatedOn>=+convert(datetime, '2010-11-25 00:00:00') AND t2.CreatedOn<=+convert(datetime, '2010-12-25 23:59:59')GROUP BY t5.ClassId,t5.ClassNumber,t2.createdon
此段代码中的“nullif('',0) AS BuyInPrice”,已经加了nullif判断,但是结果中的值还是null,用了isnull函数也一样,有什么更好的办法吗?
'' AS Name,
'' AS billsheetno,
'' AS BillSheetId,
nullif('',0) AS BuyInPrice,
'' AS xsj,
'' AS createdon,
t5.ClassId,
t5.ClassNumber+'(小计)' AS ClassNumber,
'' AS MeasurementUnit,--单位
'' AS gysname,
'' AS sku,--商品编号
SUM(t3.Quantity) AS Quantity,
SUM(nullif(t3.Quantity,0)*nullif(t3.BuyInPrice,0)) AS amount ,
SUM(nullif(t3.quantity,0)*nullif(t3.costprice,0)) as cbje
FROM dbo.Product t1 left join dbo.Inventory_Consignor t4 on t1.SupplierId=t4.ConsignorId,
dbo.Inventory_BillSheet t2 ,
dbo.Inventory_BillSheet_Item t3 ,
dbo.User_Class t5
WHERE t1.ProductId=t3.ProductId
AND t2.BillSheetId=t3.BillSheetId and t2.billtype=2 and t2.orderstatusid=7
AND t5.ClassId=t2.UserClassId
AND t1.isDurability=1
AND t2.CreatedOn>=+convert(datetime, '2010-11-25 00:00:00') AND t2.CreatedOn<=+convert(datetime, '2010-12-25 23:59:59')GROUP BY t5.ClassId,t5.ClassNumber,t2.createdon
此段代码中的“nullif('',0) AS BuyInPrice”,已经加了nullif判断,但是结果中的值还是null,用了isnull函数也一样,有什么更好的办法吗?
哪位高人有更好的办法解决吗?
SELECT '' AS ProductId,
'' AS Name,
'' AS billsheetno,
'' AS BillSheetId,
ISNULL(BuyInPrice,0) AS BuyInPrice,
'' AS xsj,
'' AS createdon,
t5.ClassId,
t5.ClassNumber+'(小计)' AS ClassNumber,
'' AS MeasurementUnit,--单位
'' AS gysname,
'' AS sku,--商品编号
SUM(t3.Quantity) AS Quantity,
SUM(ISNULL(t3.Quantity,0)*ISNULL(t3.BuyInPrice,0)) AS amount ,
SUM(ISNULL(t3.quantity,0)*ISNULL(t3.costprice,0)) as cbje