-- 資料設定
DECLARE @Table AS TABLE
(
[Id] INT IDENTITY(1, 1)
, [Type] TINYINT
, [TypeOne] INT
, [TypeTwo] INT
, [TypeThree] INT
) ;
INSERT INTO @Table
( [Type]
, [TypeOne]
, [TypeTwo]
, [TypeThree] )
VALUES
( 1, 1, NULL, NULL )
, ( 1, 2, NULL, NULL )
, ( 1, 3, NULL, NULL )
, ( 2, NULL, 10, NULL )
, ( 2, NULL, 20, NULL )
, ( 3, NULL, NULL, 100 )
, ( 3, NULL, NULL, 200 )
, ( 3, NULL, NULL, 300 ) ;
-- 查詢過濾器
DECLARE @IncludeTypeOne BIT = 1
, @IncludeTypeTwo BIT = 0 ;
DECLARE @TypeThree_Ids TABLE ( [TypeThree] INT ) ;
INSERT INTO @TypeThree_Ids
VALUES
( 200 )
, ( 300 ) ;
-- 目標：@Table根據@IncludeTypeOne、@IncludeTypeTwo和@TypeThree_Ids值進行查詢。對于前兩個過濾器，無需檢查[TypeOne]和[TypeTwo]列中型別的特定值。但是，對于第三個過濾器，[TypeThree]列中的特定值必須與 中的值匹配@TypeThree_Ids。有沒有辦法做到這一點，而無需執行三個單獨的查詢并將它們全部合并在一起（實際的表/資料非常大）？
-- 預期輸出
Id Type TypeOne TypeTwo TypeThree
1 1 1 NULL NULL
2 1 2 NULL NULL
3 1 3 NULL NULL
7 3 NULL NULL 200
8 3 NULL NULL 300
-- 到目前為止我的失敗嘗試
SELECT *
FROM @Table
WHERE ( ( @IncludeTypeOne = 0 AND [Type] <> 1 ) OR [Type] = 1 )
AND ( ( @IncludeTypeTwo = 0 AND [Type] <> 2 ) OR [Type] = 2 )
AND ( ( ( SELECT COUNT(1) FROM @TypeThree_Ids ) = 0 AND [Type] <> 3 ) OR [TypeThree] IN ( SELECT [TypeThree] FROM @TypeThree_Ids ) ) ;
-- 實際輸出
Id Type TypeOne TypeTwo TypeThree
uj5u.com熱心網友回復：
使用連接比使用子查詢更好——像這樣：
SELECT *
FROM @Table
LEFT JOIN @TypeThreeIds ON @Table.TypeThree= @TypeThreeIds.TypeThree
WHERE (@includetypeone = 1 AND [Type] = 1)
OR (@includetypetype = 2 AND [Type] = 2)
OR ([Type] = 3 AND @TypeThreeIds.TypeThree IS NOT NULL)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/533008.html
標籤：sqlsql服务器tsqlsql-server-2016
上一篇：在node.js中，如何清理未通過輸入驗證的api請求以供將來安全審查
下一篇：如何連接/合并不同長度的兩列？