英文:
How to get the Not Exists Record List from the Given List in Presto with ANSI SQL
问题
如何从给定的输入条件中获取不存在记录列表？
如果我们使用“Not IN”运算符，它将从表中返回所有不匹配的记录。但我想要从给定的输入条件中获取不匹配的记录。
我知道这个答案适用于Oracle数据库，但这是专门针对Presto的。有人可以帮忙吗？
select i.column_value as country_code
from table(SYS.DBMS_DEBUG_VC2COLL('AU', 'IN', 'ZA', 'DK', 'CH', 'NL')) i
where not exists
(select null
from country c
where c.country_code = i.column_value)
英文:
How to get the list of Not Exists Record List from the Given Input Condition?
If we are using Not IN Operator, it will result all the not matching records from the table. But I want to get the not matching record from the Given Input Condition.
I know this answer is there for oracle database but this is exclusively needed for Presto. Can someone help on this?
select i.column_value as country_code
from table(SYS.DBMS_DEBUG_VC2COLL('AU', 'IN', 'ZA', 'DK', 'CH', 'NL')) i
where not exists
(select null
from country c
where c.country_code = i.column_value)
答案1
得分: 1
Oracle的SYS.DBMS_DEBUG_VC2COLL函数用于将逗号分隔的列表转换为行的列表，就像从表中选择一样。
在Presto中，一个"未嵌套"的数组应该用于替代，例如：
SELECT t.country_code
FROM UNNEST(ARRAY['AU', 'IN', 'ZA', 'DK', 'CH', 'NL']) AS t(country_code)
WHERE NOT EXISTS (
SELECT 1
FROM country c
WHERE c.country_code = t.country_code
)
英文:
The Oracle SYS.DBMS_DEBUG_VC2COLL function is used to transform a comma-separated list into a list of rows, just like a select from a table.
An "unnested" array in Presto should substitute for this, e.g.:
SELECT t.country_code
FROM UNNEST(ARRAY['AU', 'IN', 'ZA', 'DK', 'CH', 'NL']) AS t(country_code)
WHERE NOT EXISTS (
SELECT 1
FROM country c
WHERE c.country_code = t.country_code
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论