SQL 如何提取多个 相同字段 语句？

Question

如下3个字段，如何提取国家代码其中带有 5，并且相同序号中有TW的字段

    customer              code                              nation  
    123456                5,6,7                               TW,CN,HK
    234567                4,5,7,9                             GB,TW,HK,JP
    345678                4,4,7                                GB,GB,HK
    456789                4,5,7,5                             GB,TW,HK,TW
    567890                4,5,7,9                             GB,CN,HK,JP

    希望查找到code 中是5，相同位置的nation是TW ,请问如何写这个SQL语句呀？
        如根据该规则，则应当是如下：
        customer              code                              nation  
    123456                5,6,7                               TW,CN,HK
    234567                4,5,7,9                             GB,TW,HK,JP
    456789                4,5,7,5                             GB,TW,HK,TW

    但是则不是
    345678                4,4,7                                GB,GB,HK     
    因为code中无5 因此不符合标准

    567890                4,5,7,9                             GB,CN,HK,JP
    因为code中虽然含有5，但是对应的nation中的位置却是CN  因此不符合标准

Answer 1

select * from table where code like '%5%' and nation like '%TW%'
你说的是这样吗
select * from customer where (customer,5) in
(select customer,codevalue from (
select t.customer,code,lengthnum,level,REGEXP_SUBSTR(code,'[^,]+',1,level,'i') as codevalue from (select customer,code,LENGTH(code)-LENGTH(REGEXP_REPLACE(code, ',', ''))+1 as lengthnum from customer where customer='123456'or customer='678910') t connect by level<lengthnum+1
group by customer,code,lengthnum,level order by customer
) x) and (customer,'TW') in
(select customer,nationvalue from (
select t.customer,nation,lengthnum,level,REGEXP_SUBSTR(nation,'[^,]+',1,level,'i') as nationvalue from (select customer,nation,LENGTH(nation)-LENGTH(REGEXP_REPLACE(nation, ',', ''))+1 as lengthnum from customer where customer='123456'or customer='678910') t connect by level<lengthnum+1
group by customer,nation,lengthnum,level order by customer
) y )
这样应该可以了，就是看起来写的有点不太好