2022 更新:Snowflake 现在支持 EXCLUDE():
测试:
with data as (
select 1 col_a, 2 col_b, 3 col_c, 4 col_d
)
select *, col_a as id
exclude (col_c, col_b, col_a)
from data
- https://twitter.com/felipehoffa/status/1593311749100294144 https://twitter.com/felipehoffa/status/1593311749100294144
之前的回答:
代替EXCEPT
语法,我编写了一个存储过程,可以为您提供列列表SELECT
for:
create or replace procedure cols_except(table_name varchar, except varchar)
returns varchar
language sql as
begin
describe table identifier(:table_name);
return (
select listagg("name", ', ') cols_except
from table(result_scan(last_query_id()))
where not array_contains("name"::variant, (split(:except, ',')))
);
end;
在删除与 except 子句匹配的值后,您可以使用它来获取要选择的列:
call cols_except('snowflake_sample_data.tpch_sf1.nation', 'N_NAME,N_REGIONKEY');