SQL 如何为不返回任何行的查询设置默认行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/285666/
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 set a default row for a query that returns no rows?
提问by John Baughman
I need to know how to return a default row if no rows exist in a table. What would be the best way to do this? I'm only returning a single column from this particular table to get its value.
如果表中不存在行，我需要知道如何返回默认行。什么是最好的方法来做到这一点？我只是从这个特定的表中返回一个列来获取它的值。
Edit: This would be SQL Server.
编辑：这将是 SQL Server。
回答by WW.
One approach for Oracle:
Oracle 的一种方法：
SELECT val
FROM myTable
UNION ALL
SELECT 'DEFAULT'
FROM dual
WHERE NOT EXISTS (SELECT * FROM myTable)
Or alternatively in Oracle:
或者在 Oracle 中：
SELECT NVL(MIN(val), 'DEFAULT')
FROM myTable
Or alternatively in SqlServer:
或者在 SqlServer 中：
SELECT ISNULL(MIN(val), 'DEFAULT')
FROM myTable
These use the fact that MIN()returns NULLwhen there are no rows.
这些使用在没有行时MIN()返回的事实NULL。
回答by Dave Costa
If your base query is expected to return only one row, then you could use this trick:
如果您的基本查询预计只返回一行，那么您可以使用以下技巧：
select NVL( MIN(rate), 0 ) AS rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
(Oracle code, not sure if NVL is the right function for SQL Server.)
（Oracle 代码，不确定 NVL 是否适合 SQL Server。）
回答by John Lemp
This would be eliminate the select query from running twice and be better for performance:
这将消除 select 查询运行两次并提高性能：
Declare @rate int
select
@rate = rate
from
d_payment_index
where
fy = 2007
and payment_year = 2008
and program_id = 18
IF @@rowcount = 0
Set @rate = 0
Select @rate 'rate'
回答by beach
How about this:
这个怎么样：
SELECT DEF.Rate, ACTUAL.Rate, COALESCE(ACTUAL.Rate, DEF.Rate) AS UseThisRate
FROM
(SELECT 0) DEF (Rate) -- This is your default rate
LEFT JOIN (
select rate
from d_payment_index
--WHERE 1=2 -- Uncomment this line to simulate a missing value
--...HERE IF YOUR ACTUAL WHERE CLAUSE. Removed for testing purposes...
--where fy = 2007
-- and payment_year = 2008
-- and program_id = 18
) ACTUAL (Rate) ON 1=1
Results
结果
Valid Rate Exists
有效率存在
Rate Rate UseThisRate
----------- ----------- -----------
0 1 1
Default Rate Used
使用的默认费率
Rate Rate UseThisRate
----------- ----------- -----------
0 NULL 0
Test DDL
测试 DDL
CREATE TABLE d_payment_index (rate int NOT NULL)
INSERT INTO d_payment_index VALUES (1)
回答by Cade Roux
One table scan method using a left join from defaults to actuals:
使用从默认值到实际值的左连接的一种表扫描方法：
CREATE TABLE [stackoverflow-285666] (k int, val varchar(255))
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-1')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-2')
INSERT INTO [stackoverflow-285666]
VALUES (1, '1-3')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-1')
INSERT INTO [stackoverflow-285666]
VALUES (2, '2-2')
DECLARE @k AS int
SET @k = 0
WHILE @k < 3
BEGIN
SELECT @k AS k
,COALESCE(ActualValue, DefaultValue) AS [Value]
FROM (
SELECT 'DefaultValue' AS DefaultValue
) AS Defaults
LEFT JOIN (
SELECT val AS ActualValue
FROM [stackoverflow-285666]
WHERE k = @k
) AS [Values]
ON 1 = 1
SET @k = @k + 1
END
DROP TABLE [stackoverflow-285666]
Gives output:
给出输出：
k Value
----------- ------------
0 DefaultValue
k Value
----------- ------------
1 1-1
1 1-2
1 1-3
k Value
----------- ------------
2 2-1
2 2-2
回答by John Baughman
I figured it out, and it should also work for other systems too. It's a variation of WW's answer.
我想通了，它也应该适用于其他系统。这是WW答案的变体。
select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18
union
select 0 as rate
from d_payment_index
where not exists( select rate
from d_payment_index
where fy = 2007
and payment_year = 2008
and program_id = 18 )
回答by Eike
This snippet uses Common Table Expressions to reduce redundant code and to improve readability. It is a variation of John Baughman's answer.
此代码段使用通用表表达式来减少冗余代码并提高可读性。这是约翰鲍曼答案的变体。
The syntax is for SQL Server.
语法适用于 SQL Server。
WITH products AS (
SELECT prod_name,
price
FROM Products_Table
WHERE prod_name LIKE '%foo%'
),
defaults AS (
SELECT '-' AS prod_name,
0 AS price
)
SELECT * FROM products
UNION ALL
SELECT * FROM defaults
WHERE NOT EXISTS ( SELECT * FROM products );
回答by Jason Anderson
Do you want to return a full row? Does the default row need to have default values or can it be an empty row? Do you want the default row to have the same column structure as the table in question?
你想返回一整行吗？默认行需要有默认值还是可以是空行？您是否希望默认行与相关表具有相同的列结构？
Depending on your requirements, you might do something like this:
根据您的要求，您可能会执行以下操作：
1) run the query and put results in a temp table (or table variable) 2) check to see if the temp table has results 3) if not, return an empty row by performing a select statement similar to this (in SQL Server):
1) 运行查询并将结果放入临时表 (或表变量) 2) 检查临时表是否有结果 3) 如果没有，通过执行类似于此的选择语句（在 SQL Server 中）返回一个空行：
select '' as columnA, '' as columnB, '' as columnC from #tempTable
Where columnA, columnB and columnC are your actual column names.
其中 columnA、columnB 和 columnC 是您的实际列名。
回答by Y-Mi Wong
Insert your default values into a table variable, then update this tableVar's single row with a match from your actual table. If a row is found, tableVar will be updated; if not, the default value remains. Return the table variable.
将您的默认值插入到表变量中，然后使用实际表中的匹配项更新此 tableVar 的单行。如果找到一行，tableVar 将被更新；如果不是，则保留默认值。返回表变量。
---=== The table & its data
CREATE TABLE dbo.Rates (
PkId int,
name varchar(10),
rate decimal(10,2)
)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (1, 'Schedule 1', 0.1)
INSERT INTO dbo.Rates(PkId, name, rate) VALUES (2, 'Schedule 2', 0.2)
Here's the solution:
这是解决方案：
---=== The solution
CREATE PROCEDURE dbo.GetRate
@PkId int
AS
BEGIN
DECLARE @tempTable TABLE (
PkId int,
name varchar(10),
rate decimal(10,2)
)
--- [1] Insert default values into @tempTable. PkId=0 is dummy value
INSERT INTO @tempTable(PkId, name, rate) VALUES (0, 'DEFAULT', 0.00)
--- [2] Update the single row in @tempTable with the actual value.
--- This only happens if a match is found
UPDATE @tempTable
SET t.PkId=x.PkId, t.name=x.name, t.rate = x.rate
FROM @tempTable t INNER JOIN dbo.Rates x
ON t.PkId = 0
WHERE x.PkId = @PkId
SELECT * FROM @tempTable
END
Test the code:
测试代码：
EXEC dbo.GetRate @PkId=1 --- returns values for PkId=1
EXEC dbo.GetRate @PkId=12314 --- returns default values
