如何从返回的子查询中选择没有值的记录(具有逗号分隔的值)? [英] How to select records (which have comma separated values) which do not have a value from the subquery returned?

问题描述

更新 SQL FIDDLE链接 https://www. db-fiddle.com/f/9t63on5kYWUNrHqXkThb1P/4

UPDATE SQL FIDDLE link https://www.db-fiddle.com/f/9t63on5kYWUNrHqXkThb1P/4

输出应仅给出以下内容(表1的第2行)

Output should give only the following (row 2 of table1)

I0016,I0028,I0045,I0056,I0215,I0321,I0361,I0369,I0420

我应该

select column1 from table1 
where <any comma separated value in column1> not in
(select col2 from table2 where col1 = 'e')

---

首选解决方案是本机SQL，并且不针对任何供应商.如有必要，可提供Spark sql函数帮助.

---

Preferable solution is native SQL, and nothing vendor specific. If necessary, spark sql functions help.

注意:我知道这是不好的设计，但这是我无法控制的.

NOTE: I understand this is bad design, but this is out of my hands.

注意 FIDDLE中的表是使用MySQL的默认设置创建的.我不知道如何在后端创建表.这就是为什么我指定这不应该是特定于供应商的原因.

NOTE The table in FIDDLE is created using default setting of MySQL. I do not know how the tables are created at the backend. That is why I am specifying that this should not be vendor specific.

推荐答案

以下相关子查询适用于我的情况.可以在上面的小提琴中进行测试.

The following correlated subquery works for my case. Can test it out in the fiddle above.

select *
from table1 as t1
where (
    select  t2.col2
    from    table2  as  t2
    where   t1.column1 like concat('%', t2.col2 ,'%')
    and t2.col1 = 'e'
    limit 1
    ) is NULL;

这篇关于如何从返回的子查询中选择没有值的记录(具有逗号分隔的值)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！