用另一张表中的一列替换表中的一列，然后选择* [英] Replace one column in a table with a column in a different table and select *

问题描述

为标题有些混乱而道歉，我一直在努力寻找问题的答案，部分原因是很难在标题行中简明扼要地描述它或很难找到一个合适的搜索字符串.哎呀，这是我面临的问题:

Apologies for the somewhat confusing Title, I've been struggling to find an answer to my question, partly because it's hard to concisely describe it in the title line or come up with a good search string for it. Anyhoooo, here's the problem I'm facing:

问题的简短版本是: 如何用Oracle可以理解的有效SQL编写以下(无效但可以理解的SQL):

Short version of the question is: How can I write the following (invalid but understandable SQL) in valid SQL understood by Oracle:

select B.REPLACER as COL, A.* except A.COL from A join B on a.COL = B.COL;

这里是长版本(如果您已经从阅读短版本中知道了我想要的内容，则无需阅读此:P):

Here's the long version (if you already know what I want from reading the short version, you don't need to read this :P ):

我的任务(简化)是提供服务，该服务对表的数据进行按摩并将其作为子查询提供.该表有很多列(几十个或更多)，我坚持使用选择*"，而不是一一列出所有列，因为没有我，新列可能会添加到表中或从表中删除.知道，尽管我的下游系统会知道并做出相应调整.

My (simplified) task is to come up with service that massages a table's data and provide it as a sub-query. The table has a lot of columns (a few dozens or more), and I am stuck with using "select *" rather than explicitly listing out all columns one by one, because new columns may be added to or removed from the table without me knowing, although my downstream systems will know and adjust accordingly.

说，此表(从现在起将其称为表A)具有称为"COL"的列，我们需要将该COL中的值替换为表B的REPLACER列中的值，其中两个COL值匹配.

Say, this table (let's call it Table A from now on) has a column called "COL", and we need to replace the values in that COL with the value in the REPLACER column of table B where the two COL value matches.

我该怎么做?我无法重命名该列，因为下游系统期望使用"COL".我不能没有"expect A.COL"部分，因为那会导致sql模棱两可.

How do I do this? I cannot rename the column because the downstream systems expect "COL"; I cannot do without the "expect A.COL" part because that would cause the sql to be ambiguous.

感谢您的帮助，万能的StackOverflow

Appreciate your help, almighty StackOverflow

雷

推荐答案

您可以使用table.*或table.fieldName.

table.* (except field X)没有可用的语法.

这意味着您只能通过显式列出所有字段来获得所需的内容...

This means that you can only get what you want by explicitly listing all of the fields...

select
  A.field1,
  A.field2,
  B.field3,
  A.field4,
  etc
from
  A join B on a.COL = B.COL;

这意味着您可能需要重新建模数据，以确保您不会继续获取新字段. OR编写动态sql.询问数据库以找出列名，使用代码如上所述编写查询，然后运行该动态生成的查询.

This means that you may need to re-model your data so as to ensure you don't keep getting new fields. OR write dynamic sql. Interrogate the database to find out the column names, use code to write a query as above, and then run that dynamically generated query.

这篇关于用另一张表中的一列替换表中的一列，然后选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！