Добавить пустую строку для запроса результатов, если результатов не найдено

18

Я пишу хранимые procs, которые вызываются унаследованной системой. Одним из ограничений устаревшей системы является то, что в едином результирующем наборе, возвращаемом из сохраненного процесса, должна быть хотя бы одна строка. Стандарт должен вернуть ноль в первом столбце (да, я знаю!).

Очевидный способ добиться этого - создать временную таблицу, поместить в нее результаты, протестировать любые строки в таблице temp и либо вернуть результаты из таблицы temp, либо один пустой результат.

Другим способом может быть выполнение EXISTS против того же предложения where, которое находится в основном запросе до выполнения основного запроса.

Ни одно из них не очень удовлетворительно. Может ли кто-нибудь подумать о лучшем пути. Я думал о таких типах 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 29.07.2010 в 20:13
источник
  • Рассмотрите рассмотренный ответ после прочтения решения @ swe! Это так хорошо, я признаю, что я почувствовал желание высунуть вас сюда;) stackoverflow.com/a/32586119/2979473 –  ensisNoctis 27.04.2016 в 12:30
  • @ensisNoctis, если я не ошибаюсь, это решение будет работать только в ситуациях, когда вы ожидаете либо нулевой, либо один результат назад –  Chris Simpson 04.05.2016 в 19:45
  • К счастью, вы - пожалуйста, позвольте мне поделиться с вами этим кодом, который в настоящее время находится в производстве в моей компании, благодаря StackOverflow;) Не могу вместить все это в этот комментарий, поэтому я опубликовал другой ответ, но это все еще @ swe's, только отформатирован лучше. stackoverflow.com/a/37046650/2979473 Он возвращает строки из нужной таблицы, если они существуют (любое число> = 1), в противном случае строки из другой таблицы (или буква, как в моем примере). –  ensisNoctis 05.05.2016 в 11:11
  • Ага, полностью пропустил намек WITH TIES. Это очень интересно, спасибо –  Chris Simpson 06.05.2016 в 01:11

6 ответов

10

Это старый вопрос, но у меня такая же проблема. Решение действительно просто БЕЗ двойного выбора:

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» вы получаете ВСЕ строки (все они имеют 1 как «orderdummy», поэтому все являются связями), или если результата нет, вы получаете свое значение по умолчанию.

    
ответ дан swe 15.09.2015 в 14:28
  • Это потрясающе! Простой, быстрый и способ лучше, чем принятый ответ, поскольку он не использует временные таблицы, не выполняет двойных запросов и не объявляет переменные (поэтому их можно легко использовать в встроенных TVF)! –  ensisNoctis 27.04.2016 в 12:29
28

Очень мало вариантов, которых я боюсь.

Вы всегда должны прикоснуться к таблице дважды, будь то COUNT, EXISTS before, EXISTs в разделе UNION, 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 будет пересекать все строки, чтобы их действительно считать

    
ответ дан gbn 29.07.2010 в 20:19
  • Есть новый бит информации для меня: EXISTS останавливается, когда находит строку. Спасибо @gbn! –  p.campbell 29.07.2010 в 20:23
  • +1: My WITH / TOP не смог вернуть фиктивную строку, когда у WITH нет данных. –  OMG Ponies 29.07.2010 в 20:47
  • Да, я определенно предпочитаю использовать EXISTS, поскольку это быстрее. Просто стыдно, что я должен написать то же самое, где предложение дважды. Думаю, я, вероятно, в конечном итоге пойду за этим, хотя, спасибо. –  Chris Simpson 29.07.2010 в 21:08
  • Если у вас длинный сложный запрос, который вы не хотите повторять, вы можете сделать что-то вроде:; WITH cte AS (...) ... SELECT * FROM cte UNION ALL SELECT 0, '' WHERE NOT EXISTS (SELECT TOP 1 * FROM cte) –  Outside the Box Developer 13.03.2015 в 00:46
2

Вы можете использовать полное внешнее соединение. Что-то из-за ...

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 18.10.2011 в 00:17
  • Это действительно интересное решение, позволяющее дважды записывать предложение where. Мне было бы интересно узнать, работает ли это и какова стоимость исполнения? Я не могу понять, почему вы завершаете основной запрос в производной таблице. Это необходимо? –  Chris Simpson 18.10.2011 в 02:17
  • Основная обертка запроса связана с предложением where, но может быть переписана в зависимости от того, как вы структурируете базовый запрос. Это вы не присоединяетесь к правильной области (постфильтрация), тогда критерии применимы к обеим таблицам. Расходы кажутся минимальными (0% в фактическом плане выполнения), однако у меня есть довольно сложный запрос. –  Andrew Jansen 18.10.2011 в 03:57
  • Да, это имеет больше смысла, теперь вы включили isnull. благодаря –  Chris Simpson 18.10.2011 в 20:01
1

Это ответ @ 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
)
    
ответ дан ensisNoctis 05.05.2016 в 11:08
0

Я думаю, вы могли бы попробовать:

Declare @count int
set @count = 0

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

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

Недостатком является то, что вы эффективно выполняете свой запрос дважды, а вверху - то, что вы пропускаете таблицу temp.

    
ответ дан AllenG 29.07.2010 в 20:17
  • Это аналогичный метод для утверждения EXISTS, предложенного мной в вопросе. Разница в том, что счет немного менее эффективен. –  Chris Simpson 29.07.2010 в 21:10
0

Чтобы избежать дублирования запроса выбора, как насчет таблицы temp для сохранения результата запроса? И на основе временной таблицы возвращайте строку по умолчанию, если таблица temp пуста или возвращает temp, когда она имеет результат?

    
ответ дан JieLi 25.04.2014 в 20:09