RICHEER COCA 2022-01-19 22:57 采纳率: 93.9%
浏览 195
已结题

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

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

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

img

  • 写回答

7条回答 默认 最新

  • Hello World, 2022-01-20 16:44
    关注

    查询的字段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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(6条)

报告相同问题?

问题事件

  • 已结题 (查看结题原因) 2月3日
  • 已采纳回答 1月27日
  • 修改了问题 1月20日
  • 修改了问题 1月20日
  • 展开全部

悬赏问题

  • ¥15 App的会员连续扣费
  • ¥15 不同数据类型的特征融合应该怎么做
  • ¥15 用proteus软件设计一个基于8086微处理器的简易温度计
  • ¥15 用联想小新14Pro
  • ¥15 multisim中关于74ls192n和DSWPK开关仿真图分析(减法计数器)
  • ¥15 基于8255的交通灯设计
  • ¥15 w3wp,exe 中发生未处理的 Microsoft ,NETFramework 异常。
  • ¥20 C51单片机程序及仿真(加减器)
  • ¥15 AQWA | 水动力分析 二阶波浪力
  • ¥15 c语言题目:成绩管理系统