blocks|key|971250|text|我想你可以试试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971251|Declare+@count+int
set+@count+=+0

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

if(@Count+=+0)+
+++select+0
else+//run+your+query|code-block|syntax|javascript|971252|缺点是您实际上运行了两次查询，而优点是您跳过了临时表。|971253|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I guess you could try:

<pre><code>Declare @count int
set @count = 0

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

if(@Count = 0) 
 select 0
else //run your query
</code></pre>

The downside is that you're effectively running your query twice, the up side is that you're skiping the temp table.

blocks|key|1185701|text|恐怕只有很少的选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185702|无论是COUNT、EXISTS+before、EXISTs+in+UNION、TOP子句等，您总是需要触摸表两次|1185703|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)|code-block|syntax|javascript|1185704|EXISTS解决方案比COUNT更好，因为它会在找到行时停止。COUNT将遍历所有行以对它们进行实际计数|1185705|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Very few options I'm afraid.

You always have to touch the table twice, whether COUNT, EXISTS before, EXISTs in UNION, TOP clause etc

<pre><code>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)
</code></pre>

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

blocks|key|971410|text|您可以使用完整的外部联接。对……有影响的事情|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971411|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+++|code-block|syntax|javascript|971412|目前，我自己正在对这个场景进行性能测试，所以不确定这会产生什么样的影响，但它会给出一个填充了Category的空白行。|971413|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use a full outer join. Something to the effect of ...

<pre><code>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 
</code></pre>

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.

blocks|key|1185853|text|为了避免重复selecting查询，先用临时表存储查询结果如何？根据临时表，如果临时表为空则返回默认行，或者当有结果时返回临时行？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1185854|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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?

blocks|key|971580|text|这是一个老问题，但我也有同样的问题。解决方案非常简单，不需要双击：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971581|select+top(1)+WITH+TIES+*+FROM+(
select
id,+category,+1+as+orderdummy
from+#test
where+category+=+@category
union+select+0,+'',+2)+ORDER+BY+orderdummy|code-block|syntax|javascript|971582|通过"WITH+tie“你可以得到所有的行(所有行都有一个1作为"orderdummy"，所以所有的行都是并列)，或者如果没有结果，你就得到默认的结果。|971583|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

It's an old question, but i had the same problem.
Solution is really simple WITHOUT double select:

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

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.

blocks|key|971637|text|这是@swe的答案，只是格式更好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|971638|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'system@company.com',+NULL,+NULL,+2),
++++++++++++(N'author@company.com',+NULL,+NULL,+2)
++++++++)+defaults([To],+CC,+BCC,+Priority)
++++)+emails
++++ORDER+BY+Priority
)|code-block|syntax|javascript|971639|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is @swe's answer, only formatted better.

<pre><code>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'system@company.com', NULL, NULL, 2),
 (N'author@company.com', NULL, NULL, 2)
 ) defaults([To], CC, BCC, Priority)
 ) emails
 ORDER BY Priority
)
</code></pre>

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.

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):

<pre><code>--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
</code></pre>

Add empty row to query results if no results found

性能测试

我正在编写一个被遗留系统调用的存储过程。遗留系统的约束之一是，从存储的proc返回的单个结果集中必须至少有一行。标准是在第一列中返回一个零(是的，我知道！)。实现这一点的明显方法是创建一个临时表，将结果放入其中，测试临时表中的任何行，然后返回临时表中的结果或单个空结果。另一种方法可能是在执行主查询之前对主查询中的同一w...

问如果未找到结果，则在查询结果中添加空行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果未找到结果，则在查询结果中添加空行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果未找到结果，则在查询结果中添加空行
EN