oracle 添加表示其他两个Varchar列的串联的列

hgqdbh6s  于 2022-12-11  发布在  Oracle
关注(0)|答案(5)|浏览(98)

我有一个employees表,我想添加第三列,该列的值为名和姓的连接,名为“FullName”。如何在不丢失前两列中任何一列的数据的情况下完成该操作?

hvvq6cgz

hvvq6cgz1#

  • Quick preface: this answer was based on the originally incorrect tag that this question was relating to SQL Server. I'm no longer aware of its validity on Oracle SQL Developer.*
ALTER TABLE Employees ADD FullName AS (FirstName + ' ' + LastName)

Although in practice I'd advise that you do that operation in your SELECT . That's somewhat personal preference, but I tend to think doing things in your end queries is a bit cleaner, more readable, and easier to maintain than storing extra, calculated columns.

Edit:

This was eventually found as the answer, and listed by the OP as a comment on this post. The following is appropriate syntax for Oracle Sql Database.

ALTER TABLE emps MODIFY (FULL_NAME VARCHAR2(50) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL);
2w2cym1i

2w2cym1i2#

如果在从数据库中选择时始终需要fullname列,则可以在创建employee表时创建计算列。
例如:

CREATE TABLE Employee
(
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  FullName AS CONCAT(FirstName,' ',LastName)
)

INSERT INTO Employee VALUES ('Rocky','Jeo')

SELECT * FROM Employee 

  Output:

  FirstName  LastName  FullName
  Rocky      Jeo       Rocky Jeo
92dk7w1h

92dk7w1h3#

这取决于您的目的,您是真的需要向数据库中添加新列,还是只需要根据需要查询出“全名”。
要动态查看,只需运行查询即可
SELECT firstname + ' ' + lastname AS FullName FROM employees
除此之外,您还可以创建一个简单的存储过程来存储它。

ttisahbt

ttisahbt4#

(For单一结果在where条件中使用等于)

select * 
from TABLE_name 
where (Column1+Column2) in (11361+280,11365+250)
nue99wik

nue99wik5#

In addition to @Jacky 's answer, if you are trying to add this to a query and not the table, there is also the CONCAT() function that you can use in the select statement

SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM table_name

相关问题