SQL Server 2008 - 用多列中的值替换代码

Question

我有以下表格：

表_1：

Value_ID     Value_Code_1      Value_Code_2       Value_Code_3
1            465               325                129

表_2：

ID          Text
465         this is a value
325         this value is a different one
129         hello world

我需要将表_1 中的代码替换为表_2 中的文本。我思考解决这个问题的方法似乎很长而且毛茸茸的，有没有更快、更好的方法来做到这一点？我必须对大约 40 列而不是我的示例中的 3 列执行此操作。

我的代码：

Select
    first.ID,
    first.string_value,
    second.string_value,
    third.string_value
from
(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_1 = b.ID
) first

left join on first.id = second.id

(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_2 = b.ID
) second

left join on first.id = third.id
(
    Select
        a.ID,
        b.text as string_value
    From
        table_1 a
        left join
        table_2 b
        on a.Value_Code_2 = b.ID
) third

谢谢。

Answer 1

您可以做得更简单，如下所示：

Select
    Table_1.ID,
    v1.Text,
    v2.Text,
    v3.Text
FROM Table_1
LEFT JOIN Table_2 v1 ON Table_1.Value_code_1 = v1.id
LEFT JOIN Table_2 v2 ON Table_1.Value_code_2 = v2.id
LEFT JOIN Table_2 v3 ON Table_1.Value_code_3 = v3.id;

您仍然需要对所有 40 列执行此操作。

Answer 2

我不知道这是否比 40 个连接的性能更高或更低，或者它是否更容易，但这是一个值得考虑的选项...

SELECT
  Table1.Value_ID,
  MAX(CASE WHEN Table1.Value_Code_1 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text1,
  MAX(CASE WHEN Table1.Value_Code_2 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text2,
  MAX(CASE WHEN Table1.Value_Code_3 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text3,
  MAX(CASE WHEN Table1.Value_Code_4 = Table2.ID THEN Table2.Text ELSE NULL END) AS Text4,
  etc
FROM
  Table1
LEFT JOIN
  Table2
    ON Table2.ID = Table1.Value_Code_1
    OR Table2.ID = Table1.Value_Code_2
    OR Table2.ID = Table1.Value_Code_3
    OR Table2.ID = Table1.Value_Code_4
GROUP BY
  Table1.Value_ID

如果您要规范化您的 Table1，它会变得更加容易.. （

New Table1:
> Value_ID,
> Column_ID,
> Value_Code

New SQL:
SELECT
  Table1.Value_ID,
  MAX(CASE WHEN Table1.Column_ID = 1 THEN Table2.Text ELSE NULL END) AS Text1,
  MAX(CASE WHEN Table1.Column_ID = 2 THEN Table2.Text ELSE NULL END) AS Text2,
  MAX(CASE WHEN Table1.Column_ID = 3 THEN Table2.Text ELSE NULL END) AS Text3,
  MAX(CASE WHEN Table1.Column_ID = 4 THEN Table2.Text ELSE NULL END) AS Text4,
  etc
FROM
  Table1
LEFT JOIN
  Table2
    ON Table2.ID = Table1.Value_Code
GROUP BY
  Table1.Value_ID

这与使用 UNPIVOT 的答案类似）