如何编写使用通配符映射进行转换的 SQL 查询 [英] How to write an SQL query that uses wild-card map to do transformation

问题描述

我有一个包含许多列的源表 A.我想将源表转换为目标表.我想要一个映射表，该表的列与源表 A 相同，行组成翻译.

I have a source table A with a number of columns. I want to do a transformation of the source table to a target table. I would like to have a mapping table with same columns as the source table A and with rows that make up the translations.

这是表 A 的示例:

COL1    COL2    COL3
aktie   ja         2
aktie   nej        3
obli    ja         2

这里是映射表

COL1    COL2    COL3        TRANSFORM
aktie   ja      NULL            3
aktie   NULL    NULL            4

现在，我们的想法是将源表与映射连接起来并返回转换后的值.NULL 的使用应作为通配符.所以我想要的结果应该是表 A 中的第一行将匹配映射表中的第一行并返回值 3

Now, the idea is to join the source table with the mapping and get the transformed value returned. The use of NULL should serve as a wildcard. So my desired result should be that the first row in table A would match the first row in the mapping table and return the value 3

对于第二行 - 这是我的挑战 - 我希望它匹配映射表中的第二行，因为它不与已经具有值的行匹配(这将导致转换后的值 3)并且作为第二个映射行在第二列中为 NULL，应将其视为通配符(尽管也考虑了映射表中的其他行).

For the second row - and here is my challenge - I want it to match the second row in the mapping table because it is NOT matched by rows that have a value already (which would result in the transformed value 3) and as the second mapping row has NULL in column two, it should be treated as a wildcard (although taking into account other rows in the mapping table as well).

我的第一次尝试是这样的

My first attempt would be something like

select A.*, m.res
from tab1 A
inner join mapping m on t.col1 = isnull(m.col1, t.col1) 
                     and t.col2 = isnull(m.col2, t.col2)
and ...

但问题是 isnull(..,..) 将匹配所有内容，并且不仅返回匹配项，除了会导致不同转换的列出的可能值.

but the problem is that the isnull(..,..) will match everything and not only return matches except the listed possible values that would result in a different transformation.

我正在寻找一种通用解决方案，它适用于具有任意数量列的任何表，而不仅仅是这里提到的这个特定的表布局.

I am looking for a generic solution that would Work for any table with any number of columns, not only this particular table-layout mentioned here.

我一直在思考这个问题，但似乎无法真正想出解决方案，所以请帮忙:)

I have been thinking a lot about this and cannot really seem to come up with the solution, so please help :)

推荐答案

这是一种使用 CTE 实现此目的的方法(适用于 SQL Server 和 Oracle)

Here is one way to do this using a CTE (will work on SQL Server and Oracle)

WITH Map3 as 
( -- TRANSFORM WILL BE NULL IF A MATCH WAS NOT MADE
   SELECT T1.COL1, T1.COL2, T1.COL3, M.TRANSFORM
   FROM Table1 T1
   LEFT JOIN Mapping M ON T1.COL1 = M.COL1 
                      AND T1.COL2 = M.COL2
                      AND T1.COL3 = COALESCE(M.COL3, T1.COL3)
), Map2 as
(
   SELECT T1.COL1, T1.COL2, T1.COL3, COALESCE(T1.TRANSFORM,M.TRANSFORM)
   FROM Map3 T1
   LEFT JOIN Mapping M ON T1.COL1 = M.COL1 
                      AND T1.COL2 = COALESCE(M.COL2, T1.COL2)
                      AND T1.TRANSFORM IS NULL
)
SELECT * 
FROM Map2

我相信它的工作原理以及如何将其扩展"到更多列是很清楚的.

I believe it is clear how this works and how to "extend" this to more columns.

如果您不能使用 CTE，这在功能上是相同的，并且可以任意嵌套:

If you can't use a CTE this is functionally the same and can be nested as far as you like:

SELECT T1.COL1, T1.COL2, T1.COL3, COALESCE(T1.TRANSFORM,M.TRANSFORM)
FROM (
   -- TRANSFORM WILL BE NULL IF A MATCH WAS NOT MADE
   SELECT T1.COL1, T1.COL2, T1.COL3, M.TRANSFORM
   FROM Table1 T1
   LEFT JOIN Mapping M ON T1.COL1 = M.COL1 
                      AND T1.COL2 = M.COL2
                      AND T1.COL3 = COALESCE(M.COL3, T1.COL3)
) T1
LEFT JOIN Mapping M ON T1.COL1 = M.COL1 
                   AND T1.COL2 = COALESCE(M.COL2, T1.COL2)
                   AND T1.TRANSFORM IS NULL

这篇关于如何编写使用通配符映射进行转换的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！