如何为已存在的SQL视图新增列，解决视图已存在报错问题

直接执行CREATE VIEW语句时，数据库会校验同名对象是否存在，你当前库中已经存在VW_my_table_test视图，因此触发报错。

使用CREATE OR REPLACE VIEW语法替代原有CREATE VIEW，无需提前删除视图，不会破坏视图原有权限、上下游依赖关系，是修改视图定义的标准方案。

CREATE OR REPLACE VIEW VW_my_table_test AS
select
extract_dt,
path,
split_part(`path`, '/', 5) `DataBAse`,
modificationtime,
to_timestamp(accesstime, 'yyyy-MM-dd HH:mm') as accesstime ,
"replication",
-- 新增列注意：你提供的逻辑用的是访问时间，和列名「未修改天数」语义不符，若是笔误可将括号内accesstime改为modificationtime
datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm'))AS QT_DIAS_SEM_MODIFICACAO,
datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm'))AS QT_DIAS_SEM_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 31 then '1' ELSE '0' END AS IN_INF_HOT_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) > 30 and datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 121 then '1' ELSE '0' END AS IN_INF_COLD_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) > 120 then '1' ELSE '0' END AS IN_INF_FROZEN_ACESSO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) < 31 then '1' ELSE '0' END AS IN_INF_HOT_MODIFICACAO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) > 30 and datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(accesstime, 'yyyy-MM-dd HH:mm')) < 121 then '1' ELSE '0' END AS IN_INF_COLD_IN_INF_HOT_MODIFICACAO,
CASE WHEN datediff(from_unixtime(unix_timestamp(now()),'yyyy-MM-dd'),to_timestamp(modificationtime, 'yyyy-MM-dd HH:mm')) > 120 then '1' ELSE '0' END AS IN_INF_FROZEN_IN_INF_HOT_MODIFICACAO,
from_unixtime(unix_timestamp(now()),'yyyy-MM-dd') as dt
from my_table_test

如果你的引擎不支持CREATE OR REPLACE VIEW语法，再考虑先删后建的方案，操作前必须确认：

• 没有正在运行的任务依赖该视图，否则删除到重建的间隙会触发查询失败
• 你拥有该视图的DROP权限
  操作代码如下：

-- 先删除视图
DROP VIEW IF EXISTS VW_my_table_test;
-- 再执行上面完整的CREATE VIEW语句即可

内容的提问来源于stack exchange，提问作者Eduardo