查询表和表中存储的列名 [英] Query a table and a column name stored in a table
问题描述
我来这里的原因是我在Oracle数据库中经验不足,无法解决.让我解释一下:
I come here because of a problem that I am not experienced enough to solve, in an Oracle Database. Let me explain:
我有一个表,我们将其称为 Attributes ,其中包含3列: ID ,属性的ID, -Entity_ID以及它引用的实体/EDIT-, Table_name ,其中包含存储属性值的表的名称; Column_name ,其中包含列的名称存储在该表中的值.
I have a table that we'll call Attributes, containing 3 columns : ID, the id of the attribute, - Entity_ID as well, the entity it refers to /EDIT-, Table_name, containing the name of the table in which the value of the attribute is stored, and Column_name, containing the name of the column in that table in which is the value is stored.
列 Table_name 中引用的每个表都包含相同的列名(例如 Value1 , Value2 ,等等.)除了第一个引用属性链接到的另一个实体( Entity_id )的对象.
Every tables referenced in the column Table_name, contains the same column names (such as Value1,Value2, etc..) except for the first one that references another entity (Entity_id), to which the attribute is linked to.
我的目标是建立一个查询,以选择每个属性(基于其ID)及其值.但是我不知道该怎么查询,因为表名和列名都发生了变化. 有没有使用变量的方法?但是,如果是这样,我如何将它们放入查询中,以便它自动针对每一行进行更改?
My goal is to build a query that selects every attribute (based on its id) and its value. But what I don't know is how to query that since the name of table and of the column changes. Is there a way to use variables? But if so, how can I put them in the query so that it automatically changes for each row?
编辑
ATTRIBUTES table
ID ENTITY_ID TABLE_NAME COLUMN_NAME
---------- -------------- ------------ -----------
1 3 Values_A Value_1
2 2 Values_B Value_3
3 2 Values_A Value_2
VALUES_A table
ENTITY_ID Value_1 Value_2 Value_3
---------- -------------- ------------ -----------
1 Monday 42 Green
2 Sunday 3000 Blue
3 Wednesday 1 Black
VALUES_B table
ENTITY_ID Value_1 Value_2 Value_3
---------- -------------- ------------ ------------
1 Tuesday 26 Green
2 Saturday 3 Red
3 Wednesday 15 White
所以我要寻找的结果是:
So the result I'm looking for would be:
RESULT:
ID Value
--------- -----------
1 Wednesday
2 Red
3 3000
对不起,如果观看起来很痛苦,制作起来也很痛苦(找不到更好的格式化方法)
Sorry if this is painful to watch, it was as painful to make (didn't find how to format it better)
推荐答案
使用Peter M的查询来构建SQL文本,然后利用XML的强大功能:
Using Peter M's query to build the SQL text, and then harnessing the dark power of XML:
create table attributes (id, entity_id, table_name, column_name)
as
select 1, 3, 'VALUES_A', 'VALUE_1' from dual union all
select 2, 2, 'VALUES_B', 'VALUE_3' from dual union all
select 3, 2, 'VALUES_A', 'VALUE_2' from dual;
create table values_a (entity_id, value_1, value_2, value_3)
as
select 1, 'Monday', 42, 'Green' from dual union all
select 2, 'Sunday', 3000, 'Blue' from dual union all
select 3, 'Wednesday', 1, 'Black' from dual;
create table values_b (entity_id, value_1, value_2, value_3)
as
select 1, 'Tuesday', 26, 'Green' from dual union all
select 2, 'Saturday', 3, 'Red' from dual union all
select 3, 'Wednesday', 15, 'White' from dual;
查询:
with queries as
( select table_name, column_name, entity_id
, 'select '|| column_name || ' as c from ' || table_name ||
' where entity_id = ' || entity_id ||
case
when id = max_id then ''
else ' union all '
end as sqltext
from
( select a.*, max(a.id) over (order by id) max_id from attributes a ) )
select table_name, column_name, entity_id
, extractvalue(xmltype(dbms_xmlgen.getxml(sqltext)),'/ROWSET/ROW/C') as sql_result
from queries;
结果:
TABLE_NAME COLUMN_NAME ENTITY_ID SQL_RESULT
---------- ----------- ---------- ---------------------------------------------------
VALUES_A VALUE_1 3 Wednesday
VALUES_B VALUE_3 2 Red
VALUES_A VALUE_2 2 3000
这篇关于查询表和表中存储的列名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!