如果不存在行，如何让 SQL Server 返回默认值 0？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1438884/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get SQL Server to return a default of 0, if no rows exist?
提问by Dominic Zukiewicz
How can I get a default value of 0 if a sum does not return any rows?
如果总和不返回任何行，如何获得默认值 0？
Edit: I have edited this post to add a more thorough example (which the previous one didn't encounter)
编辑：我编辑了这篇文章以添加一个更彻底的例子（前一个没有遇到）
E.g.
例如
DECLARE @Item TABLE
(
Id int,
Price decimal,
PricePer decimal
)
DECLARE @OrderItem TABLE
(
Id int,
ItemId int,
ChargedPrice nvarchar(10),
QtyRequired int,
QtyLeftToDespatch int
)
INSERT INTO @Item (Id,Price,PricePer) VALUES (1,1.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (2,2.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (3,3.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (4,4.00, 1)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 1,1,100,100,50 )
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 2,1,200,300,50)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 3,1,300,300,50 )
DECLARE @ItemIdTest int
SET @ItemIdTest = 4
SELECT SUM((price/priceper)*QtyRequired) as total_value,
SUM((price/priceper)*QtyRequired) as outstanding_value
FROM @Item i
INNER JOIN @OrderItem o ON i.Id = o.ItemId
WHERE i.Id = @ItemIdTest
group by itemId
This will return
这将返回
total_value, outstanding_value
==============
<No rows>
But I want it to have 0 as the default value returned. However, if you do a SELECT to select it, then a second SELECT to select a default, 2 result sets will be returned.
但我希望它有 0 作为返回的默认值。但是，如果您执行 SELECT 以选择它，则第二个 SELECT 选择默认值，将返回 2 个结果集。
Can I do it just in one? I have looked at COALESCE, but this only works if NULL is returned, which it isn't.
我可以一次完成吗？我看过 COALESCE，但这仅在返回 NULL 时才有效，而事实并非如此。
Edit: I think the problem is to do with the group with, but is there a reason why still it doesn't come back with any results?
编辑：我认为问题与组有关，但是有没有理由为什么它仍然没有返回任何结果？
采纳答案by bleeeah
DECLARE @Item TABLE
(
Id int,
Price decimal,
PricePer decimal
)
DECLARE @OrderItem TABLE
(
Id int,
ItemId int,
ChargedPrice nvarchar(10),
QtyRequired int,
QtyLeftToDespatch int
)
INSERT INTO @Item (Id,Price,PricePer) VALUES (1,1.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (2,2.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (3,3.00, 1)
INSERT INTO @Item (Id,Price,PricePer) VALUES (4,4.00, 1)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 1,1,100,100,50 )
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 2,1,200,300,50)
INSERT INTO @OrderItem (Id, ItemId,ChargedPrice,QtyRequired,QtyLeftToDespatch) VALUES ( 3,1,300,300,50 )
DECLARE @ItemIdTest int
SET @ItemIdTest = 4
SELECT COALESCE(SUM((price/priceper)*QtyRequired),0) as total_value,
COALESCE(SUM((price/priceper)*QtyRequired),0) as outstanding_value
FROM @Item i
LEFT OUTER JOIN @OrderItem o ON i.Id = o.ItemId
WHERE i.Id = @ItemIdTest
group by itemId
回答by Adriaan Stander
Try this
尝试这个
SELECT ISNULL(SUM(Debt) ,0)
FROM SupplierDebt
WHERE Id = @TestId
回答by bleeeah
Or use
或使用
SELECT COALESCE(SUM(Debt) ,0)
FROM SupplierDebt
WHERE Id = @TestId
COALESCE is an ANSI standard, if that bothers you and can take multiple parameters.
COALESCE 是 ANSI 标准，如果这让您感到困扰并且可以采用多个参数。
回答by n8wrl
SQL 2k5: this worked for me:
SQL 2k5：这对我有用：
isnull(sum(Dept), 0)
