时间戳

首页 > 解决方案 > 内连接条件：当第一个表中的值为“ALL”时，它与第二个表中的所有值连接？

sql-server - 内连接条件：当第一个表中的值为“ALL”时，它与第二个表中的所有值连接？

问题描述

我需要在多个值上加入 2 个表。

当第一个表中的值为“ALL”时，它应该连接第二个表中的所有值。如果是任何其他值，它应该正常加入。

我需要加入 5 个不同的列，其中 4 个可能具有值“ALL”（Column1、Column2、Column3 和 Column4），因此我编写了以下由 16 个部分查询组成的查询。

它有效，但我认为必须有更好的方法来做到这一点。我该如何优化它？

示例图像连接

示例图像结果

SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP     
INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                         AND PP.Column1 = MV.Column1
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 = 'ALL'

UNION ALL

SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                         AND PP.Column2 = MV.Column2
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 = 'ALL'

UNION ALL

SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                         AND PP.Column3 = MV.Column3
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 = 'ALL'

UNION ALL

SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                         AND PP.Column4 = MV.Column4
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column2 = MV.Column2
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 = 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column3 = MV.Column3
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 = 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column3 = MV.Column3
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column3 = MV.Column3
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 = 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column3 = MV.Column3
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 = 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column3 = MV.Column3
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column3 = MV.Column3
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
                                             AND PP.Column1 = MV.Column1
                                             AND PP.Column2 = MV.Column2
                                             AND PP.Column3 = MV.Column3
                                             AND PP.Column4 = MV.Column4
WHERE MV.Column1 != 'ALL'
  AND MV.Column2 != 'ALL'
  AND MV.Column3 != 'ALL'
  AND MV.Column4 != 'ALL'
UNION ALL
SELECT *
FROM [Temporaer-Prozessplan-Resultat] AS PP
     INNER JOIN [Mapping-Lean-Vergaben] AS MV ON PP.Column5 = MV.Column5
WHERE MV.Column1 = 'ALL'
  AND MV.Column2 = 'ALL'
  AND MV.Column3 = 'ALL'
  AND MV.Column4 = 'ALL';

标签： sql-server

解决方案

我应用了 Mahesh 的建议，最终查询如下所示：

select * from [Temporaer-Prozessplan-Resultat] as PP        
inner join      
[Mapping-Lean-Vergaben] as MV       
on      
    PP.column5=MV.column5   

where   1= case when MV.column1 ='ALL' then 1 
              when PP.column1=MV.column1 then 1
          else 0
          end
and
        2= case when MV.column2  ='ALL' then 2 
              when PP.column2 =MV.column2  then 2
          else 0
          end

and
        3= case when MV.column3  ='ALL' then 3 
              when PP.column3 =MV.column3  then 3
          else 0
          end

and
        4= case when MV.column4 ='ALL' then 4 
              when PP.column4=MV.column4 then 4
          else 0
          end

推荐阅读