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

问题描述

这是我想要做的 - 我有一个包含很多列的表，并且想要创建一个视图，其中一个列根据其他列中的某些值组合重新分配，例如

Name, Age, Band, Alive ,,, <其他字段太多)

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

选择*，年龄=案例当姓名"=BRYAN ADAMS"和活着"= 1 THEN 18其他年龄"结尾发件人表

然而，我现在拥有的模式是Name, Age, Band, Alive,,,,,, Age

我可以在我的选择语句中使用AS"来实现姓名、年龄、乐队、活着、、、、<太多>、、年龄_计算.

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

是否有选择性重命名，我可以将SELECT * and A_1 as A, B_1 as b?(然后 A_1 完全消失)或选择性 * 在那里我可以选择除某些列之外的所有列?(这也将解决上一个陈述中提出的问题)

我知道枚举所有列并创建适当查询的hacky方式，但我仍然希望有一种更简单"的方法来做到这一点.

解决方案

抱歉，不，没有办法根据需要使用 SELECT * 结构替换现有列名.

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

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

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

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

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

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.

解决方案

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

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屋！