为同一行计算多个条件

dfddblmv 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(272)

我必须比较两个不同的来源，并确定所有的不匹配
IDs Source_excel table

+-----+-------------+------+----------+
| id  | name        | City | flag     |
+-----+-------------+------+----------+
| 101 | Plate       | NY   | Ready    |
| 102 | Back washer | NY   | Sold     |
| 103 | Ring        | MC   | Planning |
| 104 | Glass       | NMC  | Ready    |
| 107 | Cover       | PR   | Ready    |
+-----+-------------+------+----------+
``` `Source_dw` table

+-----+----------+------+----------+
| id | name | City | flag |
+-----+----------+------+----------+
| 101 | Plate | NY | Planning |
| 102 | Nut | TN | Expired |
| 103 | Ring | MC | Planning |
| 104 | Top Wire | NY | Ready |
| 105 | Bolt | MC | Expired |
+-----+----------+------+----------+

预期结果

+-----+-------------+----------+------------+----------+------------+---------+------------------+
| ID | excel_name | dw_name | excel_flag | dw_flag | excel_city | dw_city | RESULT |
+-----+-------------+----------+------------+----------+------------+---------+------------------+
| 101 | Plate | Plate | Ready | Planning | NY | NY | FLAG_MISMATCH |
| 102 | Back washer | Nut | Sold | Expired | NY | TN | NAME_MISMATCH |
| 102 | Back washer | Nut | Sold | Expired | NY | TN | FLAG_MISMATCH |
| 102 | Back washer | Nut | Sold | Expired | NY | TN | CITY_MISMATCH |
| 103 | Ring | Ring | Planning | Planning | MC | MC | ALL_MATCH |
| 104 | Glass | Top Wire | Ready | Ready | NMC | NY | NAME_MISMATCH |
| 104 | Glass | Top Wire | Ready | Ready | NMC | NY | CITY_MISMATCH |
| 107 | Cover | | Ready | | PR | | MISSING IN DW |
| 105 | | Bolt | | Expired | | MC | MISSING IN EXCEL |
+-----+-------------+----------+------------+----------+------------+---------+------------------+

我尝试了下面的查询，但它只给出了一个不匹配。

select ISNULL(EXCEL.ID,DW.ID) ID,
excel.name as excel_name,dw.name as dw_name,
excel.flag as excel_flag,dw.flag as dw_flag,
excel.city as excel_city,dw.city as dw_city,
RESULT = CASE WHEN excel.ID IS NULL THEN 'MISSING IN EXCEL'
WHEN dw.ID IS NULL THEN 'MISSING IN DW'
WHEN excel.NAME<>dw.NAME THEN 'NAME_MISMATCH'
WHEN excel.CITY<>dw.CITY THEN 'CITY_MISMATCH'
WHEN excel.FLAG <> dw.FLAG THEN 'FLAG_MISMATCH'
ELSE 'ALL_MATCH' END
from source_excel excel
FULL OUTER JOIN source_dw dw ON excel.id=dw.id

实际产量

我明白 `case` 表达式只检查第一个满足的条件。有没有其他方法可以检查所有的情况？

sql sql-server unpivot case full-outer-join

来源：https://stackoverflow.com/questions/63227760/evaluate-multiple-conditions-for-same-row

2条答案

按热度按时间

jbose2ul1#

如果我没听错，您希望每个不匹配都有一行，或者有一行表示所有内容都匹配。
你可以用 cross apply 生成行，如下所示：

SELECT 
    COALESCE(xl.ID, dw.ID) ID, 
    xl.name as excel_name,dw.name as dw_name,
    xl.flag as excel_flag,dw.flag as dw_flag,
    xl.city as excel_city,dw.city as dw_city,
    x.result
FROM source_excel xl
FULL OUTER JOIN source_dw dw ON xl.id = dw.id
CROSS APPLY (VALUES
    (CASE WHEN xl.ID IS NULL           THEN 'MISSING IN EXCEL' END),
    (CASE WHEN dw.ID IS NULL           THEN 'MISSING IN DW'    END),
    (CASE WHEN WHEN xl.NAME <> dw.NAME THEN 'NAME_MISMATCH'    END),
    (CASE WHEN xl.CITY <> dw.CITY      THEN 'CITY_MISMATCH'    END),
    (CASE WHEN xl.FLAG <> dw.FLAG      THEN 'FLAG_MISMATCH'    END),
    (CASE WHEN 
        xl.ID = dw.ID 
        AND xl.NAME = dw.NAME
        AND xl.CITY = dw.CITY
        AND xl.FLAG = dw.FLAG
    THEN 'ALL_MATCH' END)
) x(result)
WHERE x.result IS NOT NULL

赞(0）回复(0）举报 2021-07-24

yks3o0rb2#

我将把不匹配的情况浓缩成一行，把原因连在一起：

select COALESCE(EXCEL.ID, DW.ID) as ID, 
       excel.name as excel_name,dw.name as dw_name,
       excel.flag as excel_flag,dw.flag as dw_flag,
       excel.city as excel_city,dw.city as dw_city,
       (CASE WHEN excel.ID IS NULL
             THEN 'MISSING IN EXCEL'
             WHEN dw.ID IS NULL
             THEN 'MISSING IN DW'
             WHEN excel.NAME = dw.NAME AND excel.CITY = dw.CITY AND excel.FLAG = dw.FLAG
             THEN 'ALL MATCH'
             ELSE CONCAT(CASE WHEN excel.NAME <> dw.NAME THEN 'NAME_MISMATCH; ' END,
                         CASE WHEN excel.CITY <> dw.CITY THEN 'CITY_MISMATCH; ' END,
                         CASE WHEN excel.FLAG <> dw.FLAG THEN 'FLAG_MISMATCH;' END
                        )
        END)
from source_excel excel FULL OUTER JOIN
     source_dw dw
     ON excel.id = dw.id;

赞(0）回复(0）举报 2021-07-24

我来回答

为同一行计算多个条件

2条答案

相关问题

热门标签

最新问答