重命名SQL中的SELECT *中的单列，选择除列之外的所有列 [英] Rename single column in SELECT * in SQL, select all but a column

问题描述

这是我要尝试的操作-我有一个包含很多列的表，并想创建一个视图，其中包含根据其他列中值的特定组合重新分配的一个列.

here is what I'm trying to do- I have a table with lots of columns and want to create a view with one of the column reassigned based on certain combination of values in other columns, e.g.

Name, Age, Band, Alive ,,, <too many other fields)

我想要一个将重新分配其中一个字段的查询，例如

And i want a query that will reassign one of the fields, e.g.

Select *, Age = 
CASE When "Name" = 'BRYAN ADAMS' AND "Alive" = 1 THEN 18
     ELSE "Age"
END
FROM Table

但是，我现在拥有的架构是Name, Age, Band, Alive,,,,<too many>,, Age

However, the schema that I now have is Name, Age, Band, Alive,,,,<too many>,, Age

我可以在我选择的陈述中使用"AS" Name, Age, Band, Alive,,,,<too many>,, Age_Computed.

I could use 'AS' in my select statment to make it Name, Age, Band, Alive,,,,<too many>,, Age_Computed.

但是，我想达到的原始架构 Name, Age, Band, Alive.,,,,其中年龄"实际上是计算出的年龄.

However, I want to reach the original schema of Name, Age, Band, Alive.,,,, where Age is actually the computed age.

可以在SELECT * and A_1 as A, B_1 as b上进行选择性重命名吗? (然后A_1完全消失) 还是选择性*，我可以选择除某些列以外的所有列? (这也将解决上一个语句中提出的问题)

Is there a selective rename where I can do SELECT * and A_1 as A, B_1 as b? (and then A_1 completely disappears) or a selective * where I can select all but certain columns? (which would also solve the question asked in the previous statement)

我知道枚举所有列并创建适当查询的方法，但是我仍然希望有一种更简单"的方法来实现此目的.

I know the hacky way where I enumerate all columns and create an appropriate query, but I'm still hopeful there is a 'simpler' way to do this.

推荐答案

对不起，没有，没有一种方法可以根据需要使用SELECT *构造替换现有的列名.

Sorry, no, there is not a way to replace an existing column name using a SELECT * construct as you desire.

总是最好明确地定义列，尤其是对于视图，并且永远不要使用SELECT *.创建视图时，只需将表的DDL用作模型.这样，您可以更改所需的任何列定义(如您的问题)，并消除不适合该视图的列.我们使用这种技术来屏蔽或消除包含敏感数据(例如社会保险号和密码)的列. marc_s在评论中提供的链接很不错.

It is always better to define columns explicitly, especially for views, and never use SELECT *. Just use the table's DDL as a model when you create the view. That way you can alter any column definition you want (as in your question) and eliminate columns inappropriate for the view. We use this technique to mask or eliminate columns containing sensitive data like social security numbers and passwords. The link provided by marc_s in the comments is a good read.

这篇关于重命名SQL中的SELECT *中的单列，选择除列之外的所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！