SQL SERVER 用多条件如何进行批量查询?请专家答疑解惑

Question

网页有问题 提示 ： 禁止重复输入同样的词汇或符号：m

只好先上传截图，再想办法上传代码

Answer 1

查询的字段DQ_RpNr1,DQ_RpNr2,DQ_RpNr3,DQ_RpNr4可以动态生成，现在示例中使用CET表只能查询一次，改成实体表、表变量或者临时表都可以生成，组成动态SQL再exec就可以了。
另外如果t3的ID不是连续的话可以先生成一个行号来代替ID

;with t3 as(
select '1'ID, '14'm1,'15'm2,'17'm3,'18'm4,'21'm5,'54'm6,'64'm7,'66'm8
union all select  '2','15','17','18','54','64','66','77','80' 
union all select '3','18','25','27','28','54','64','66','77' 
union all select '4','8','35','37','38','54','64','66','77' 
)
----  表 t4 有多行数据 需要对表 #T4 进行查询 
,t4 as(
select '11'm1,'12'm2,'21'm3,'44'm4,'53'm5,'56'm6,'57'm7,'63'm8,'64'm9,'80'm10
union all select '7','11','12','21','53','56','57','63','64','80'
union all select '7','8','11','21','53','56','57','63','64','80'
union all select '7','8','11','18','21','53','56','57','64','80'
union all select '7','8','11','18','21','53','57','64','77','80'
union all select '7','8','18','21','53','54','57','64','77','80'
union all select '7','8','18','21','53','54','57','64','66','77'
union all select '7','8','15','18','21','53','54','64','66','77'
union all select '17','18','25','27','28','54','64','66','74','77'
union all select '7','8','35','37','38','54','64','66','74','77'
),
t4WithSeq AS(SELECT *,ROW_NUMBER() OVER(ORDER BY t4.m1) seq FROM t4)
, CTE AS (
SELECT pe.ID, QTY from  t3
 unpivot (QTY for TYPE in(m1,m2,m3,m4,m5,m6,m7,m8)) as pe
), list AS (
Select 
 ((Case when m1 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m2 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m3 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m4 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m5 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m6 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m7 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m8 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m9 in (Select Qty from CTE where ID=1) then 1 else 0 end)
+ (Case when m10 in (Select Qty from CTE where ID=1) then 1 else 0 end)
) AS Val, 1 AS ID, CAST( 'DQ_RpNr1' AS VARCHAR(100)) AS ColName,t4WithSeq.seq
FROM t4WithSeq
UNION ALL Select 
 ((Case when m1 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m2 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m3 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m4 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m5 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m6 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m7 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m8 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m9 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
+ (Case when m10 in (Select Qty from CTE where ID=b.ID+1) then 1 else 0 end)
) AS Val, b.ID+1 AS ID, CAST( 'DQ_RpNr' + CAST(b.id+1 AS VARCHAR(10)) AS VARCHAR(100)) AS ColName,a.seq
FROM t4WithSeq a INNER join list b on a.seq=b.seq WHERE EXISTS(SELECT * from cte c where c.ID=b.ID+1)
)
SELECT p.seq, DQ_RpNr1,DQ_RpNr2,DQ_RpNr3,DQ_RpNr4 FROM (SELECT val,colname,seq from list) l PIVOT (MAX(val) FOR ColName IN (DQ_RpNr1,DQ_RpNr2,DQ_RpNr3,DQ_RpNr4)) p ORDER BY p.seq