blocks|key|732721|text|下面的select将通过表1上的主键和表2上的外键来连接表。select+*+from+table1+inner+join+table2+on+table1.id1+=+table2.id1；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|732722|如果我知道你的想法，下一步是用另一个表中的数据替换数据。|732723|为此，您可以编写一个脚本。|732724|I整数；|732725|开始|732726|for+I+in+(select+*+from+table1+inner+join+table2+on+table1.id1+=+table2.id1)循环|732727|update+table1
+++set+table1.col1+=+i.col2
+where+id1+++++++++=+i.id1;|code-block|syntax|javascript|732728|结束循环；|732729|结束；|732730|当然，为了您的情况，您必须更改此语句。|732731|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|12|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|13|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|14|8|@]|9|@]|A|$O|P]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

The select below you will connect tables by primary key on table 1 and foreign key on table 2.
select * from table1 inner join table2 on table1.id1 = table2.id1;

If I got your idea, next step is replace data with data from another table(s).

For this you can make a script.

i integer;

begin

for i in (select * from table1 inner join table2 on table1.id1 = table2.id1) loop

<pre><code>update table1
 set table1.col1 = i.col2
 where id1 = i.id1;
</code></pre>

end loop;

end;

Of course you have to change this statement for your case.

blocks|key|1077101|text|如果您知道模式，并准备对所有可能的列名进行硬编码，那么您可以使用case语句来强制执行：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1077102|select+case+lower(t1.column_name)
++++when+'col1'+then+col1
++++when+'col2'+then+col2
++++when+'col3'+then+col3
++++...
++++when+'coln'+then+coln
++end+as+result
from+table1+t1
join+table2+t2+on+t2.id1+=+t1.id1
where+t1.id1+=+1;|code-block|syntax|javascript|1077103|如果您需要它更灵活，那么您将需要使用动态SQL。您说要显示它，并且已经使用SQL+Developer标记了该问题，因此这可能意味着将输出显示在SQL+Worksheet+script+output窗口中-这可以使用dbms_output来完成|1077104|set+serveroutput+on
declare
++l_id1++number+:=+1;
++l_column_name+table1.column_name%25type;
++l_cur_sql+varchar2(80);
++l_cur+sys_refcursor;
++l_result+number;
begin
++select+column_name+into+l_column_name+from+table1+where+id1+=+l_id1;
++l_cur_sql+:=+'select+'+%7C%7C+l_column_name+%7C%7C+'+from+table2+'
++++%7C%7C+'where+id1+=+:id1';
++open+l_cur+for+l_cur_sql+using+l_id1;
++loop
++++fetch+l_cur+into+l_result;
++++exit+when+l_cur%25notfound;
++++dbms_output.put_line(l_result);
++end+loop;
++close+l_cur;
end;
/|1077105|或者，您可以使用SQL*Plus/SQL+Developer绑定变量处理来简化游标处理，而不依赖于dbms_output|1077106|var+cur+refcursor;
var+id+number;
exec+:id+:=+1;

declare
++l_column_name+table1.column_name%25type;
++l_cur_sql+varchar2(80);
begin
++select+column_name+into+l_column_name+from+table1+where+id1+=+:id;
++l_cur_sql+:=+'select+'+%7C%7C+l_column_name+%7C%7C+'+from+table2+'
++++%7C%7C+'where+id1+=+:id1';
++open+:cur+for+l_cur_sql+using+:id;
end;
/

print+cur|1077107|它们实际上都在做同样的事情。将列名从table1获取到一个本地变量中，并使用它创建一条动态SQL语句，以便从table2中选择该列。您可以通过直接从table1查询创建动态SQL语句来进一步简化它：|1077108|declare
++l_cur_sql+varchar2(80);
begin
++select+'select+'+%7C%7C+column_name+%7C%7C+'+from+table2+'+%7C%7C+'where+id1+=+:id1'
++into+l_cur_sql
++from+table1
++where+id1+=+:id;

++open+:cur+for+l_cur_sql+using+:id;
end;
/|1077109|但我认为分两个阶段会稍微清楚一点。|1077110|entityMap^0|W|4|0|0|2Z|B|0|0|1D|B|0|0|I|6|1I|6|22|6|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|U|3|V|5|H|7|1J|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1K|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]]|Z|$]]

If you know the schema and are prepared to hard-code all the possible column names then you can brute-force it with a <code>case</code> statement:

<pre><code>select case lower(t1.column_name)
 when 'col1' then col1
 when 'col2' then col2
 when 'col3' then col3
 ...
 when 'coln' then coln
 end as result
from table1 t1
join table2 t2 on t2.id1 = t1.id1
where t1.id1 = 1;
</code></pre>

If you need it to be more flexible then you'll need to use dynamic SQL. You said you want to display it, and you've tagged the question with SQL Developer, so that could just mean having the output in the SQL Worksheet script output window - which you could do with <code>dbms_output</code>:

<pre><code>set serveroutput on
declare
 l_id1 number := 1;
 l_column_name table1.column_name%type;
 l_cur_sql varchar2(80);
 l_cur sys_refcursor;
 l_result number;
begin
 select column_name into l_column_name from table1 where id1 = l_id1;
 l_cur_sql := 'select ' || l_column_name || ' from table2 '
 || 'where id1 = :id1';
 open l_cur for l_cur_sql using l_id1;
 loop
 fetch l_cur into l_result;
 exit when l_cur%notfound;
 dbms_output.put_line(l_result);
 end loop;
 close l_cur;
end;
/
</code></pre>

Or you could use the SQL*Plus/SQL Developer bind variable handling to simplify the cursor handling and not rely on <code>dbms_output</code>:

<pre><code>var cur refcursor;
var id number;
exec :id := 1;

declare
 l_column_name table1.column_name%type;
 l_cur_sql varchar2(80);
begin
 select column_name into l_column_name from table1 where id1 = :id;
 l_cur_sql := 'select ' || l_column_name || ' from table2 '
 || 'where id1 = :id1';
 open :cur for l_cur_sql using :id;
end;
/

print cur
</code></pre>

They're both doing the same thing really. You get the column name into a local variable from <code>table1</code>, and use it to create a dynamic SQL statement to select that column from <code>table2</code>. You can simplify it further by creating the dynamic SQL statement directly fro, the <code>table1</code> query:

<pre><code>declare
 l_cur_sql varchar2(80);
begin
 select 'select ' || column_name || ' from table2 ' || 'where id1 = :id1'
 into l_cur_sql
 from table1
 where id1 = :id;

 open :cur for l_cur_sql using :id;
end;
/
</code></pre>

But I think it's slightly clearer in two stages.

Say I have <code>Table1</code> with columns: <code>id1, name, column_name</code> 
And a <code>Table2</code> with columns: <code>id2, id1 (fk), col1, col2, col3, ..., colN</code>

This is just a simplistic example of what I am looking for. In actuality, there are multiple tables in-between these 2 tables. 

The values in <code>Table1</code> under <code>column_name</code> are string values <code>"col1", "col2", "col3", ..., "colN"</code>.

Essentially, I want a select statement that will replace the strings <code>"col1", "col2", ...</code> with the actual values of column 1, column 2, ... from <code>Table2</code>. 

Thanks.

Oracle SQL: Get value from column where column name comes from a another table

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

假设我有包含列的Table1：id1, name, column_name和一个包含列的Table2：id2, id1 (fk), col1, col2, col3, ..., colN这只是我正在寻找的一个简单的例子。实际上，在这两个表之间有多个表。column_name下的Table1中的值是字符串值"col1",...

问Oracle SQL:从列名来自另一个表的列中获取值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle SQL:从列名来自另一个表的列中获取值EN