我正在尝试创建一个单独的视图,该视图从另一个视图中获取数据,并为分类目的添加一个列;但是,正如现在,我有很多记录在该附加列下具有零值...我想知道我是否可以以某种方式管理不符合不符合任何条件的记录我用来更改视图的案例声明?预先感谢
alter view2
select col1, case when col2='love' then 'non-shop'
when col2='choose' and col3='brand' then 'non-shop'
when col2='choose' and col3<>'brand' then 'shop'
when col2='buy' then 'shop'
end traffic_type
from view1
建立我的评论:
ALTER VIEW view2 as
;WITH CTE AS
(
select col1, case when col2='love' then 'non-shop'
when col2='choose' and col3='brand' then 'non-shop'
when col2='choose' and col3<>'brand' then 'shop'
when col2='buy' then 'shop'
end as traffic_type
from view1
)
select * from CTE
where traffic_type is not null
正如@larnu所说
我会做:
alter view view2 as
select col1, v11.traffic_type
from view1 v1 cross apply
( values (case when (col2 = 'love') or (col2 = 'choose' and col3 = 'brand')
then 'non-shop'
when (col2 = 'choose' and col3 <> 'brand') or (col2 = 'buy')
then 'shop'
end)
) v11(traffic_type)
where v11.traffic_type is not null;