How to add a string into a new column based on the value of another column? (Oracle SQL Developer)

guz6ccqo 于 2022-11-03 发布在 Oracle

关注(0)|答案(2)|浏览(182)

我需要根据另一行更新新创建的行的值。在本例中，我需要创建一个名为荣誉_level的新列，并根据学生的gpa用不同的字符串填充它。这需要使用SELECT和UNION语句来完成。但我不确定是否应该使用ALTER TABLE命令创建新列，或者是否可以在单独的SELECT语句中创建新列。我只需要找到一种方法，根据学生的GPA将正确的字符串分配给新创建的列。

SELECT first_name, last_name, email, cGPA AS "1st Class Honor"
    FROM Student
    WHERE cGPA > 3.9;
UNION
SELECT first_name, last_name, email, cGPA AS "2nd Class Honor"
    FROM Student
    WHERE cGPA >= 3.7 AND cGPA < 3.9;
UNION
SELECT first_name, last_name, email, cGPA AS "3rd Class Honor"
    FROM Student
    WHERE cGPA >= 3.5 AND cGPA < 3.7;
UNION  
SELECT first_name, last_name, email, cGPA AS "On track to graduate"
    FROM Student
    WHERE cGPA < 3.5 AND cGPA > 2.0;

oracle

来源：https://stackoverflow.com/questions/73983747/how-to-add-a-string-into-a-new-column-based-on-the-value-of-another-column-ora

2条答案

按热度按时间

3zwjbxry1#

如果荣誉级别 * 总是 * 从cgpa派生，您还可以考虑使用派生列：

alter table student add honor_level varchar2(100) generated always as (       CASE
          WHEN cgpa > 3.9
          THEN
             '1st Class Honor'
          WHEN     cgpa >= 3.7
               AND cgpa < 3.9
          THEN
             '2nd Class Honor'
          WHEN     cgpa >= 3.5
               AND cgpa < 3.7
          THEN
             '3rd Class Honor'
          WHEN     cgpa < 3.5
               AND cgpa > 2
          THEN
             'On track to graduate'
       END) virtual;

不需要更新-始终派生值，并且无法插入/更新列。

赞(0）回复(0）举报 2022-11-03

xfb7svmp2#

我不确定是否应该使用ALTER TABLE命令创建新列，或者是否可以在单独的SELECT语句中创建新列
这取决于你真正需要的结果。
如果只想选择这些值，那么select就是：

SELECT first_name,
       last_name,
       email,
       cgpa,
       CASE
          WHEN cgpa > 3.9
          THEN
             '1st Class Honor'
          WHEN     cgpa >= 3.7
               AND cgpa < 3.9
          THEN
             '2nd Class Honor'
          WHEN     cgpa >= 3.5
               AND cgpa < 3.7
          THEN
             '3rd Class Honor'
          WHEN     cgpa < 3.5
               AND cgpa > 2
          THEN
             'On track to graduate'
       END honor_level
  FROM student;

如果要将值存储到新列中，则必须先添加该列（如果该列不存在），然后更新其内容：

ALTER TABLE student
   ADD honor_level VARCHAR2 (30);

UPDATE student
   SET honor_level =
          CASE
             WHEN cgpa > 3.9
             THEN
                '1st Class Honor'
             WHEN     cgpa >= 3.7
                  AND cgpa < 3.9
             THEN
                '2nd Class Honor'
             WHEN     cgpa >= 3.5
                  AND cgpa < 3.7
             THEN
                '3rd Class Honor'
             WHEN     cgpa < 3.5
                  AND cgpa > 2
             THEN
                'On track to graduate'
          END;

或者，您可以创建一个视图，这样您就不必重新编写相同的代码（也不必在cgpa更改时更新honor_level）：

CREATE OR REPLACE VIEW v_student
AS
   SELECT first_name,
          last_name,
          email,
          cgpa,
          CASE
             WHEN cgpa > 3.9
             THEN
                '1st Class Honor'
             WHEN     cgpa >= 3.7
                  AND cgpa < 3.9
             THEN
                '2nd Class Honor'
             WHEN     cgpa >= 3.5
                  AND cgpa < 3.7
             THEN
                '3rd Class Honor'
             WHEN     cgpa < 3.5
                  AND cgpa > 2
             THEN
                'On track to graduate'
          END honor_level
     FROM student;

赞(0）回复(0）举报 2022-11-03

我来回答

How to add a string into a new column based on the value of another column? (Oracle SQL Developer)

2条答案

相关问题

热门标签

最新问答