SQL SERVER 用多条件如何进行批量查询?请专家答疑解惑
网页有问题 提示 ： 禁止重复输入同样的词汇或符号：m
只好先上传截图，再想办法上传代码
查询的字段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
-- 已知条件一
;with t1 as(
select '1'm1,'2'm2,'3'm3
union all select '7','11','12'
)
-- 已知条件二
,t2 as(
select '7'm1,'8'm2,'18'm3,'21'm4
union all select '53','54','57','64'
union all select '15','18','21','53'
union all select '54','64','66','77'
)
-- 已知条件三
,t3 as(
select '14'm1,'15'm2,'17'm3,'18'm4,'21'm5,'54'm6,'64'm7,'66'm8,'77'm9,'88'm10
union all select '7','8','15','17','18','54','64','66','74','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 '7','8','15','17','18','21','54','64','66','77'
union all select '7','8','15','17','18','54','64','66','74','77'
)
分别取t1、t2、t3作为查询条件， 批量从表 T4里找出符合条件的结果，想得到如下结果
m1 m2 m3 m4 m5 m6 m7 m8 m9 m10
7 11 12 21 53 56 57 63 64 80 -- 符合了条件一（第2行数据）
7 8 11 18 21 53 56 57 64 80 -- 符合了条件二（第1行数据）
7 8 11 18 21 53 57 64 77 80 -- 符合了条件二（第1行数据）
7 8 18 21 53 54 57 64 77 80 -- 符合了条件二（第1、2、4行数据）
7 8 18 21 53 54 57 64 66 77 -- 符合了条件二（第1、2、4行数据）
7 8 15 18 21 53 54 64 66 77 -- 符合了条件二（第1、3、4行数据）
7 8 15 17 18 21 54 64 66 77 -- 符合了条件二（第1、3、4行数据）
7 8 15 17 18 54 64 66 74 77 -- 符合了条件三（第2行数据）
不太优雅的写法，全部条件安排上
-- 已知条件一
;with t1 as(
select '1'm1,'2'm2,'3'm3
union all select '7','11','12'
)
-- 已知条件二
,t2 as(
select '7'm1,'8'm2,'18'm3,'21'm4
union all select '53','54','57','64'
union all select '15','18','21','53'
union all select '54','64','66','77'
)
-- 已知条件三
,t3 as(
select '14'm1,'15'm2,'17'm3,'18'm4,'21'm5,'54'm6,'64'm7,'66'm8,'77'm9,'88'm10
union all select '7','8','15','17','18','54','64','66','74','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 '7','8','15','17','18','21','54','64','66','77'
union all select '7','8','15','17','18','54','64','66','74','77'
),t5 AS (SELECT ROW_NUMBER() OVER(ORDER BY t4.m1) AS seq, * FROM t4),--加序号
t6 AS(SELECT seq,tt.x FROM t5 a CROSS APPLY (VALUES (a.m1),(a.m2),(a.m3),(a.m4),(a.m5),(a.m6),(a.m7),(a.m8),(a.m9),(a.m10))tt(x))--列转行
SELECT *
FROM t5
WHERE t5.seq IN ( SELECT seq
FROM t5 a
CROSS APPLY ( SELECT *
FROM t1 c
WHERE c.m1 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m2 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m3 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )) ttt
UNION
SELECT seq
FROM t5 a
CROSS APPLY ( SELECT *
FROM t2 c
WHERE c.m1 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m2 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m3 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m4 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )) ttt
UNION
SELECT seq
FROM t5 a
CROSS APPLY ( SELECT *
FROM t3 c
WHERE c.m1 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m2 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m3 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m4 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m5 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m6 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m7 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m8 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m9 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )
AND c.m10 IN ( SELECT x FROM t6 b WHERE b.seq = a.seq )) ttt );
复制多几个啊，但这样会更快？
;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'
)
, CTE AS (
SELECT pe.ID, QTY from t3
unpivot (QTY for TYPE in(m1,m2,m3,m4,m5,m6,m7,m8)) as pe
)
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 DQ_RpNr1,
((Case when m1 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m2 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m3 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m4 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m5 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m6 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m7 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m8 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m9 in (Select Qty from CTE where ID=2) then 1 else 0 end)
+ (Case when m10 in (Select Qty from CTE where ID=2) then 1 else 0 end)
) as DQ_RpNr2,
((Case when m1 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m2 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m3 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m4 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m5 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m6 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m7 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m8 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m9 in (Select Qty from CTE where ID=3) then 1 else 0 end)
+ (Case when m10 in (Select Qty from CTE where ID=3) then 1 else 0 end)
) as DQ_RpNr3,
((Case when m1 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m2 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m3 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m4 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m5 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m6 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m7 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m8 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m9 in (Select Qty from CTE where ID=4) then 1 else 0 end)
+ (Case when m10 in (Select Qty from CTE where ID=4) then 1 else 0 end)
) as DQ_RpNr4
from t4
执行得到结果
m1 m2 m3 m4 m5 m6 m7 m8 m9 m10
7 11 12 21 53 56 57 63 64 80
7 8 18 21 53 54 57 64 77 80
7 8 18 21 53 54 57 64 66 77
7 8 15 17 18 54 64 66 74 77
感谢帮助，但结果不对吧，需要的结果是
seq m1 m2 m3 m4 m5 m6 m7 m8 m9 m10
2 7 11 12 21 53 56 57 63 64 80
4 7 8 11 18 21 53 56 57 64 80
5 7 8 11 18 21 53 57 64 77 80
6 7 8 18 21 53 54 57 64 77 80
7 7 8 18 21 53 54 57 64 66 77
8 7 8 15 18 21 53 54 64 66 77
9 7 8 15 17 18 21 54 64 66 77
10 7 8 15 17 18 54 64 66 74 77
问题出在哪儿？
没那么复杂,多个字段把它变成一个字段就是了
select * from t4 where
concat(m1,'-',m2,'-',m3) in (select concat(m1,'-',m2,'-',m3) from t1) or
concat(m1,'-',m2,'-',m3,'-',m4) in (select concat(m1,'-',m2,'-',m3,'-',m4) from t2) or
concat(m1,'-',m2,'-',m3,'-',m4,'-',m5,'-',m6,'-',m7,'-',m8,'-',m9) in (select concat(m1,'-',m2,'-',m3,'-',m4,'-',m5,'-',m6,'-',m7,'-',m8,'-',m9) from t3)
等下,我好像看漏了,你这查询条件是可以乱序的？想随机满足任意字段都行？这问题这太离谱了吧。。。究竟是怎样的程序需要这样来设计数据库呀？我太好奇了。。。