Sql-Server

處理返回多行的case語句子查詢

  • May 13, 2020
select *
from temp tp
join a 
on    tp.id = a.id
where (  a.enddate   between @startdate and @endDate
     or a.startdate between @startdate and @endDate)
and   a.id1 in (case when (isnull(@id,''<>'')
                    then (select distinct id from #temp2)
                    else a.id1
               end)

#temp2 查詢返回多行。是否可以通過 case 語句來實現，否則請盡可能採用最佳方式。

UDPATE：我使用 case 語句，因為當@id 為空時它應該返回所有記錄（a.id1=1,2,3,4,5,6）但是當@id=1,2,3 是不為空，它應該只返回那些具有 a.id1=1,2,3 的記錄。

注意#1：在不知道您使用的是哪個 RDMBS 的情況下，我不能說您提出的查詢是否有效（我傾向於“懷疑”）。

注意＃2：我假設不應該(在isnull那裡，否則我不確定您要實現什麼邏輯，即您需要使用匹配的括號更新您的查詢。

我想你會想​​要將isnull測試拉到頂層並使用 anor來滿足你的條件測試，例如：

-- using '@id in (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)

and    (   
           @id is not NULL
       or
          (@id is NULL and a.id1 in (select distinct id from #temp2) )
      )

-- or
-- using 'exists (select ...)' construct

select *
from   temp tp
join   a 
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)

and    (   
           @id is not NULL
       or
          (@id is NULL and exists(select 1 from #temp2 t where t.id=a.id1) )
      )

如果我理解正確，您有一個變數，該變數@id將包含一個以逗號分隔的 ID 值列表，即 string <>，或者為 NULL。如果它具有 ID 值，則您要搜尋的行必須具有指定的 ID 之一；否則，您要搜尋的行必須與 table 中的 ID 之一匹配#temp2。

不幸的是，您不能將字元串常量用作IN直接比較的值列表。從這一點出發，基本上有兩種方法。您可以將語句建構為動態 SQL 語句（在 ID 列表中連接），或者從字元串中解析出各個 ID，將它們載入到表中，然後使用它。

我們將使用動態 SQL，因為我認為它更容易遵循：

DECLARE @stmt NVARCHAR(max);

SET @stmt = N'SELECT *
 FROM temp tp
        INNER JOIN a ON (tp.id = a.id)
WHERE (   a.enddate BETWEEN @startdate AND @enddate
       or a.startdate BETWEEN @startdate AND @enddate
      )
and   a.id1 in ('
+ CASE WHEN ISNULL(@id,'<>') = '<>'
   THEN N'SELECT DISTINCT id FROM #temp2'
   ELSE @id
 END
+ ');'
;

EXECUTE sp_executesql @stmt
                    ,N'@startdate datetime, @enddate datetime'
                    ,@startdate = @startdate
                    ,@enddate = @enddate;

使用sp_executesql儲存過程時，需要給它一個語句來執行；您還可以給它一個參數列表，然後分配這些參數的值。在這裡，我為參數使用了相同的名稱，以及我們將用來填充它們的變數。對於語句和參數列表，sp_executesql需要nvarchar類型字元串；這就是為什麼字元串常量以 . 開頭的原因N'。

我們建構的語句是硬編碼的（帶有開始和結束日期的參數），除了IN列表中的實際內容。你的CASE聲明在那裡，如果你有一個明確的 ID 列表，如果你沒有，則放入SELECTfrom #temp2。

更新：如果您確實對動態 SQL 解決方案有疑問，那麼您可以使用儲存過程、函式或 CLR 將逗號分隔的整數值列表拆分為臨時表。有很多方法可以做到這一點：這裡有一篇文章的連結，討論了一些選項，並提供了推薦的解決方案。還有很多其他的 - 鍵入"SQL Server" split string，您將獲得大約 507,000 個結果。忽略任何談論STRING_SPLIT功能從 SQL Server 2016 開始可用，當然，這不是您的版本。鑑於您的字元串可能不會太長，任何 2016 年之前的解決方案都可能對您足夠好。如果您的 ID 列表可能增長到 1,000 多個項目，您可能需要考慮仔細查看 Aaron 的文章（上面的連結）並查看哪些選項仍然可以合理執行。

如果您使用SplitInts_IOnline_2Aaron 部落格文章中的函式：

CREATE FUNCTION dbo.SplitInts_Inline_2
(
  @List       VARCHAR(MAX),
  @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
  RETURN
  (
      SELECT Item = CONVERT(INT, (SUBSTRING(
          @Delimiter + @List + @Delimiter, 
          w.n + 1,
          CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n + 1) - w.n - 1
          )))
      FROM
      (

          SELECT n = v0.n + v1.n + v2.n + v3.n
              FROM  
              (
                  SELECT n = 0 
                  UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3
                  UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6
                  UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL SELECT 9
                  UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
                  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
              ) AS v0,
              (
                  SELECT n = 0 
                  UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48
                  UNION ALL SELECT 64  UNION ALL SELECT 80  UNION ALL SELECT 96
                  UNION ALL SELECT 112 UNION ALL SELECT 128 UNION ALL SELECT 144
                  UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192
                  UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
              ) AS v1,
              (
                  SELECT n = 0
                  UNION ALL SELECT 256  UNION ALL SELECT 512  UNION ALL SELECT 768 
                  UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 
                  UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL SELECT 2304
                  UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072
                  UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
              ) AS v2,
              (
                  SELECT n = 0
                  UNION ALL SELECT 4096   UNION ALL SELECT 8192   UNION ALL SELECT 12288
                  UNION ALL SELECT 16384  UNION ALL SELECT 20480  UNION ALL SELECT 24576
                  UNION ALL SELECT 28672  UNION ALL SELECT 32768  UNION ALL SELECT 36864
                  UNION ALL SELECT 40960  UNION ALL SELECT 45056  UNION ALL SELECT 49152
                  UNION ALL SELECT 53248  UNION ALL SELECT 57344  UNION ALL SELECT 61440
                  UNION ALL SELECT 65536  UNION ALL SELECT 69632  UNION ALL SELECT 73728
                  UNION ALL SELECT 77824  UNION ALL SELECT 81920  UNION ALL SELECT 86016
                  UNION ALL SELECT 90112  UNION ALL SELECT 94208  UNION ALL SELECT 98304
                  UNION ALL SELECT 102400 UNION ALL SELECT 106496 UNION ALL SELECT 110592
                  UNION ALL SELECT 114688 UNION ALL SELECT 118784 UNION ALL SELECT 122880 
                  UNION ALL SELECT 126976 UNION ALL SELECT 131072 UNION ALL SELECT 135168
                  UNION ALL SELECT 139264 UNION ALL SELECT 143360 UNION ALL SELECT 147456
              ) v3
      ) w
      WHERE w.n = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n)
      AND w.n < LEN(@Delimiter + @List)
  );

然後您的程式碼將如下所示：

IF ISNULL(@id,'<>' = '<>')
BEGIN
   SELECT tp.*, a.*
     FROM temp tp
            INNER JOIN a ON (tp.id = a.id)
    WHERE (   a.enddate BETWEEN @startdate AND @enddate
           or a.startdate BETWEEN @startdate AND @enddate
          )
      AND EXISTS (SELECT 1 FROM #temp2 WHERE is = a.id1)
END
ELSE
BEGIN
   SELECT tp.*, a.*
     FROM temp tp
            INNER JOIN a ON (tp.id = a.id)
              INNER JOIN dbo.SplitInts_Inline_2(@id,',') ids ON (a.id1 = ids.Item)

    WHERE (   a.enddate BETWEEN @startdate AND @enddate
           or a.startdate BETWEEN @startdate AND @enddate
          )
END;

請注意，這假設您的列表中沒有重複的值。我在函式中看不到任何重複數據刪除，因此要消除重複，您必須將上述函式的 iiner 連接替換為：

INNER JOIN (SELECT DISTINCT Item FROM dbo.SplitInts_Inline_2(@id,',')) ids ON (a.id1 = ids.Item)

另請注意，如果該表中沒有重複項，則EXISTS檢查#temp2也可以是INNER JOINto 。a我這樣做是EXISTS為了確保我們只獲得 中的行的一個副本a，即使 中存在多個匹配的行#temp2。

就像我說的，我認為動態 SQL 選項更容易遵循。

引用自：https://dba.stackexchange.com/questions/180361

相關問答

Sql-Server

如何根據 PartNumber 獲取至少有一個源類型網站和至少一個 stockid 等於 1 的元件號？

  • February 27, 2022
檢視問答
Sql-Server

即使使用非聚集索引和全文索引，SE LECT 查詢也很慢

  • February 21, 2022
檢視問答
Sql-Server

如何在不使用自連接的情況下從表類別中顯示 c 和 x 的類別名稱？

  • February 1, 2022
檢視問答
Sql-Server

並行執行儲存過程

  • October 18, 2021
檢視問答
Sql-Server

MS SQL Server 2014：我的錯誤在哪個儲存過程中？

  • April 13, 2021
檢視問答
Sql-Server

在這種情況下如何避免“無法嵌套 INSERT EXEC 語句”異常？

  • May 13, 2020
檢視問答