DECLARE @IsSearch_ BIT;
SET @IsSearch_ ='True'
DECLARE @Organization_ VARCHAR(100);
SET @Organization_ ='111,111A'
select *
from VYC20 C20
where (@Organization_ IS NULL)
OR (1 = CASE @IsSearch_
WHEN 0 THEN (CASE WHEN @Organization_ IS NOT NULL
AND C20.ORG IN
(select value
from STRING_SPLIT(@Organization_, ','))
THEN 1 ELSE 0 END)
WHEN 1 THEN (CASE WHEN C20.ORG like @Organization_ '%'
THEN 1 ELSE 0 END)
ELSE 0 END)
在上面的查詢中，第一種情況下，帶有 IN 子句的 WHEN 0 陳述句給出了正確的記錄，但是 WHEN 1 THEN 給出了 0 條記錄，有人可以幫助我如何應用以逗號分隔的字串的 like 子句。
uj5u.com熱心網友回復：
看起來你真正想要的是分裂LIKE 后使用
DECLARE @IsSearch_ BIT = 1;
DECLARE @Organization_ VARCHAR(100) = '111,111A';
SELECT *
FROM VYC20 C20
WHERE @Organization_ IS NULL
OR (@IsSearch_ = 0 AND
C20.ORG IN
(SELECT s.value
FROM STRING_SPLIT(@Organization_, ',') s))
OR (@IsSearch_ = 1 AND
EXISTS (SELECT 1
FROM STRING_SPLIT(@Organization_, ',') s
WHERE C20.ORG LIKE s.value '%'));
我強烈建議您以基于集合的方式執行此操作并使用表變數或表引數
DECLARE @IsSearch_ BIT = 1;
DECLARE @Organization_ TABLE (value VARCHAR(100));
INSERT @Organization_ VALUES('111'),('111A');
SELECT *
FROM VYC20 C20
WHERE @IsSearch_ = -1 -- include all
OR (@IsSearch_ = 0 AND
C20.ORG IN
(SELECT s.value
FROM @Organization_ s))
OR (@IsSearch_ = 1 AND
EXISTS (SELECT 1
FROM @Organization_ s
WHERE C20.ORG LIKE s.value '%'));
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331624.html
標籤：sql sql-server 查询语句
上一篇：從對話中選擇*[陣列]
下一篇：添加組的差異計數（*）