时间戳

首页 > 解决方案 > 将列名与其他表中的数据匹配

sql - 将列名与其他表中的数据匹配

问题描述

我已经动态创建了一个临时表 #temp1，它根据 json 字符串动态创建列。该表将如下所示

#temp1

ID 场地 水果 蔬菜
1 字段1 一种 乙
2 字段2 C D

我想只在基于#temp1表的结果表中填写值列，字段和type_id已经预先填写

结果表

ID 场地 type_id 价值
1 字段1 1 一种
2 字段1 2 乙
3 字段2 1 C
4 字段2 2 D

其中 type_id 可以从另一个指定类型及其 id 的表中获取

类型表

ID type_id 类型
1 1 水果
2 2 蔬菜

类型表中会有很多不同的类型，我只列出了仅在这个问题中使用的两个。

请帮忙！

我的尝试查询：

update B
        set B.value = ' value B.column_name in #result match with'
        from result_table B
        inner join type_table D
        on D.type = 'column name in result_table'

标签： sqlsql-server

解决方案

If it’s true that the result table will already have an existing record that already contains field and type_id, like you mention, then I think you could try this:

UPDATE B
SET B.value = 
    CASE WHEN
        D.type = ‘FRUIT’
    THEN
        T.FRUIT
    WHEN
        D.type = ‘VEGE’
    THEN T.VEGE
    END
FROM result_table B
INNER JOIN #temp1 T ON T.field = B.field
INNER JOIN type_table D on D.type_id = B.type_id

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bed087b6599ad2e0623b1198efcff845

However, this will require that you had an additional CASE statement per each type_id you wish to update in the future.

If it’s possible for you to change the way in which the temp table is being created and populated from the json object, then please consider structuring your temp table like this instead:

ID Field Type Value
1 field1 FRUIT A
2 field1 VEGE B
3 field2 FRUIT C
4 field2 VEGE D

Then your SQL to update the value column could simplify to this:

UPDATE B
SET B.Value = T.value
FROM result_table B
INNER JOIN type_table D on D.type_id = B.type_id
INNER JOIN #temp1 T ON T.Field = B.field AND T.type = D.type

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=87a2b650c646ea0be5e65fe1d7012227

推荐阅读