SQL 如果没有找到结果，则在查询结果中添加空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3365697/
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
Add empty row to query results if no results found
提问by Chris Simpson
I'm writing stored procs that are being called by a legacy system. One of the constraints of the legacy system is that there must be at least one row in the single result set returned from the stored proc. The standard is to return a zero in the first column (yes, I know!).
我正在编写由遗留系统调用的存储过程。遗留系统的约束之一是从存储过程返回的单个结果集中必须至少有一行。标准是在第一列中返回零（是的，我知道！）。
The obvious way to achieve this is create a temp table, put the results into it, test for any rows in the temp table and either return the results from the temp table or the single empty result.
实现这一点的显而易见的方法是创建一个临时表，将结果放入其中，测试临时表中的任何行，然后返回临时表中的结果或单个空结果。
Another way might be to do an EXISTS against the same where clause that's in the main query before the main query is executed.
另一种方法可能是在执行主查询之前对主查询中的同一个 where 子句执行 EXISTS。
Neither of these are very satisfying. Can anyone think of a better way. I was thinking down the lines of a UNION kind of like this (I'm aware this doesn't work):
这两个都不是很令人满意。谁能想到更好的办法。我在想像这样的 UNION 台词（我知道这行不通）：
--create table #test
--(
-- id int identity,
-- category varchar(10)
--)
--go
--insert #test values ('A')
--insert #test values ('B')
--insert #test values ('C')
declare @category varchar(10)
set @category = 'D'
select
id, category
from #test
where category = @category
union
select
0, ''
from #test
where @@rowcount = 0
采纳答案by swe
It's an old question, but i had the same problem. Solution is really simple WITHOUT double select:
这是一个老问题，但我遇到了同样的问题。解决方案非常简单，无需双选：
select top(1) WITH TIES * FROM (
select
id, category, 1 as orderdummy
from #test
where category = @category
union select 0, '', 2) ORDER BY orderdummy
by the "WITH TIES" you get ALL rows (all have a 1 as "orderdummy", so all are ties), or if there is no result, you get your defaultrow.
通过“WITH TIES”，您将获得所有行（所有行都有一个 1 作为“orderdummy”，所以都是关系），或者如果没有结果，您将获得默认行。
回答by gbn
Very few options I'm afraid.
恐怕选择很少。
You always have to touch the table twice, whether COUNT, EXISTS before, EXISTs in UNION, TOP clause etc
你总是要触摸表两次，无论是 COUNT、EXISTS 之前、UNION 中的 EXISTs、TOP 子句等
select
id, category
from mytable
where category = @category
union all --edit, of course it's quicker
select
0, ''
where NOT EXISTS (SELECT * FROM mytable where category = @category)
An EXISTS solution is better then COUNT because it will stop when it finds a row. COUNT will traverse all rows to actually count them
EXISTS 解决方案比 COUNT 更好，因为它会在找到一行时停止。COUNT 将遍历所有行以实际计算它们
回答by Robert Kope?
This is @swe's answer, only formatted better.
这是@swe 的答案，只是格式更好。
CREATE FUNCTION [mail].[f_GetRecipients]
(
@MailContentCode VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 1 WITH TIES -- returns all rows having highest priority found
[To],
CC,
BCC
FROM (
SELECT
[To],
CC,
BCC,
1 AS Priority -- if no rows, priority 2 under UNION will get returned
FROM mail.Recipients
WHERE 1 = 1
AND IsActive = 1
AND MailContentCode = @MailContentCode
UNION ALL
SELECT
*
FROM (VALUES
(N'[email protected]', NULL, NULL, 2),
(N'[email protected]', NULL, NULL, 2)
) defaults([To], CC, BCC, Priority)
) emails
ORDER BY Priority
)
回答by Andrew Jansen
You can use a full outer join. Something to the effect of ...
您可以使用完整的外部联接。有什么作用...
declare @category varchar(10)
set @category = 'D'
select #test.id, ISNULL(#test.category, @category) as category from (
select
id, category
from #test
where category = @category
)
FULL OUTER JOIN (Select @category as CategoryHelper ) as EmptyHelper on 1=1
Currently performance testing this scenario myself so not sure on what kind of impact this would have but it will give you a blank row with Category populated.
目前我自己对此场景进行了性能测试，因此不确定这会产生什么样的影响，但它会给你一个填充了类别的空白行。
回答by JieLi
To avoid duplicating the selecting query, how about a temp table to store the query result first? And based on the temp table, return default row if the temp table is empty or return the temp when it has result?
为了避免重复选择查询，如何先使用临时表存储查询结果？并根据临时表，如果临时表为空，则返回默认行，或者在有结果时返回临时表？
回答by AllenG
I guess you could try:
我想你可以试试：
Declare @count int
set @count = 0
Begin
Select @count = Count([Column])
From //Your query
if(@Count = 0)
select 0
else //run your query
The downside is that you're effectively running your query twice, the up side is that you're skiping the temp table.
缺点是您有效地运行了两次查询，另一方面是您跳过了临时表。
