如何在sql中使用case进行列的添加和填充？

Question

我有下面的表格

create table scientisttt (id integer, firstname varchar(100), lastname varchar(100));
        insert into scientisttt (id, firstname, lastname) values (1, 'albert', 'einstein');
        insert into scientisttt (id, firstname, lastname) values (2, 'isaac', 'newton');
        insert into scientisttt (id, firstname, lastname) values (3, 'marie', 'curie');
       insert into scientisttt (id, firstname, lastname) values (4, 'marie', 'curie');
        insert into scientisttt (id, firstname, lastname) values (5, 'isaac', 'newton');
        insert into scientisttt (id, firstname, lastname) values (6, 'isaac', 'newton');
        select * from scientisttt;

select count(*), firstname
from scientisttt
group by firstname;

select 
     * ,
     count(*) over (partition by firstname) as "Count"
into nam
from scientisttt; 

select * from nam;


select s.firstname, count into newtab from scientisttt as s left join namessss as n on s.firstname = n.firstname;

我知道如何创建一个包含额外列的新表，如下所示：

select *,
case 
when count >= 3 then 'ok'
else 'bad'
end as newcol
into newtab2
from newtab;

但是，有没有其他方法可以做到呢？

Answer 1

您可以创建视图：

create view v_scientisttt as
    select s.* ,
           (case when count(*) over (partition by firstname) > 3
                 then 'ok' else 'bad'
            end) as count
    from scientisttt s;

使用该视图的任何人都可以看到正确的值。不需要为此创建新的表。