22,207
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #TBML(ID INT IDENTITY(1,1) PRIMARY KEY ,a INT,b INT, c INT ,d INT ,e INT, f INT)
INSERT INTO #TBML(a,b,c,d,e,f)VALUES(1,2,3,4,5,6),(6,17,20,21,22,51),(10,19,20,25,26,51)
--测试数据
/*
ID a b c d e f
1 1 2 3 4 5 6
2 6 17 20 21 22 51
3 10 19 20 25 26 51
*/
DECLARE @SZ0 NVARCHAR(100) ='36,37,38,39,40'; --包含0个
DECLARE @SZ2 NVARCHAR(100) ='11,12,13,14,15,16,17,18,19,20'; --包含2个
DECLARE @SZ23 NVARCHAR(100) ='21,22,23,24,25,26'; --包含2-3个
DECLARE @SZ02 NVARCHAR(100) ='31,32,33'; --不超过2个
SELECT a.ID
, a.a
, a.b
, a.c
, a.d
, a.e
, a.f
, Condition1
, Condition2
, Condition3
, Condition4
FROM #TBML AS a
CROSS APPLY
(SELECT SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ0+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ2+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ23+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ02+',')))
FROM
(VALUES (b)
, (c)
, (d)
, (e)
, (f)) AS b1 (col) ) AS b(Condition1, Condition2, Condition3, Condition4)
WHERE Condition1=0
AND Condition2=2
AND Condition3 BETWEEN 2 AND 3
AND Condition4<=2;
/*--结果集
ID a b c d e f Condition1 Condition2 Condition3 Condition4
2 6 17 20 21 22 51 0 2 2 0
3 10 19 20 25 26 51 0 2 2 0
*/
DECLARE @SZ0 NVARCHAR(100) ='36,37,38,39,40'; --包含0个
DECLARE @SZ2 NVARCHAR(100) ='11,12,13,14,15,16,17,18,19,20'; --包含2个
DECLARE @SZ23 NVARCHAR(100) ='21,22,23,24,25,26'; --包含2-3个
DECLARE @SZ02 NVARCHAR(100) ='31,32,33'; --不超过2个
;WITH TMB
(ID, a, b, c, d, e, f)
AS
(
SELECT 1
, 10
, 20
, 30
, 40
, 50
, 60
UNION ALL
SELECT 2
, 11
, 22
, 33
, 44
, 55
, 66)
SELECT a.ID
, a.a
, a.b
, a.c
, a.d
, a.e
, a.f
, Condition1
, Condition2
, Condition3
, Condition4
FROM TMB AS a
CROSS APPLY
(SELECT SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ0+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ2+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ23+',')))
, SUM(SIGN(CHARINDEX(','+RTRIM(col) +',', ','+@SZ02+',')))
FROM
(VALUES (b)
, (c)
, (d)
, (e)
, (f)) AS b1 (col) ) AS b(Condition1, Condition2, Condition3, Condition4)
WHERE Condition1=0
AND Condition2=2
AND Condition3 BETWEEN 2 AND 3
AND Condition4<=2;
/*--不加条件时测试显示效果
ID a b c d e f Condition1 Condition2 Condition3 Condition4
1 10 20 30 40 50 60 1 1 0 0
2 11 22 33 44 55 66 0 0 1 1
*/