如果查询结果为空，则向查询结果添加空行

Question

如果查询结果为空，则向查询结果添加空行

sqlsql-serversql-server-2005t-sql

27

我正在编写被旧系统调用的存储过程。旧系统的限制之一是必须至少返回一个结果集中的行。通常情况下，在第一列返回0（是的，我知道这很奇怪！）。

实现这一点的明显方法是创建一个临时表，将结果放入其中，检查临时表中是否存在任何行并返回来自临时表或单个空结果的结果。

另一种方法可能是在主查询执行之前对相同的where子句执行一个EXISTS。

这两种方法都不太令人满意。有没有人能想出更好的方法？我认为可以像这样使用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

- Chris Simpson

6个回答

20

这是一个古老的问题，但我遇到了同样的问题。解决方案非常简单，无需使用双重选择：

select top(1) WITH TIES * FROM (
select
id, category, 1 as orderdummy
from #test
where category = @category
union select 0, '', 2) ORDER BY orderdummy

使用"WITH TIES"，您将获取所有行（所有行都有一个“orderdummy”为1，因此所有行都是并列的），或者如果没有结果，则获取默认行。

- swe

1

如果有人正在寻找在Oracle中可用的WITH TIES语法（已在19c中测试）：select * from ( your_query_here ) order by orderdummy fetch first 1 row with ties; - lightwing

4

您可以使用完全外连接。类似以下方式...

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

目前正在对这个场景进行性能测试，所以不确定这将产生什么样的影响，但它将为您提供一个已填充类别的空行。

- Andrew Jansen

1

这是一个非常有趣的解决方案，可以避免两次编写 where 子句。我想知道这是否有效以及性能成本如何？我无法弄清楚为什么要在主查询中包装一个派生表。这是必要的吗？ - Chris Simpson

1

主查询包装是由于where子句，但根据您构建底层查询的方式可能会被重写。如果您没有在正确的区域（后过滤）中加入，则条件适用于两个表。成本似乎很小（实际执行计划中为0％），但我所拥有的查询在操作上相当复杂。 - Andrew Jansen

4

这是@swe的答案，只是重新格式化了一下：

CREATE FUNCTION [mail].[f_GetRecipients]
(
    @MailContentCode VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP 1 WITH TIES -- Returns either all Priority 1 rows or, if none exist, all Priority 2 rows
        [To],
        CC,
        BCC
    FROM (
        SELECT
            [To],
            CC,
            BCC,
            1 AS Priority
        FROM mail.Recipients
        WHERE 1 = 1
            AND IsActive = 1
            AND MailContentCode = @MailContentCode

        UNION ALL

        SELECT
            *,
            2 AS Priority
        FROM (VALUES
            (N'system@company.com', NULL, NULL),
            (N'author@company.com', NULL, NULL)
        ) defaults([To], CC, BCC)
    ) emails
    ORDER BY Priority
)

- Robert Synoradzki

1

为了避免重复选择查询，可以使用临时表先存储查询结果。基于临时表，如果临时表为空，则返回默认行；如果有结果，则返回临时表的内容。

- JieLi

1

我猜你可以尝试：

Declare @count int
set @count = 0

Begin
Select @count = Count([Column])
From //Your query

if(@Count = 0) 
   select 0
else //run your query

缺点是你实际上运行了两次查询，好处是你跳过了临时表。

- AllenG

1

这是与我在问题中建议的EXISTS语句类似的方法。不同之处在于计数效率略有降低。 - Chris Simpson

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gbn · Accepted Answer

很遗憾，可选项非常少。

无论是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)

使用EXISTS比使用COUNT更好，因为它在找到行后就会停止。而COUNT则需要遍历所有行才能对其进行计数。