sql 分割对比
0
[待解决问题]
id 列1 列2
1 a,b,c,d a,b,d
2 a,b,c,e a,b,c,d,e
3 a,b,c,e a,b,c,,e
现在进行比较如果 列2 的字符传包含 列1 的所有字符传则输出
id 列1 列2
2 a,b,c,e a,b,c,d,e
3 a,b,c,e a,b,c,e
所有回答(2)
0
where col2 like '%'+ col1 + '%'
0
表这样设计应该不合理吧，不满足第二范式。将列1，列2拆分出来分成两个表
A: B: C：
Id Id,列1 Id,列2
1 1,a 1,a
1,b 1,d
......................
如果你是oracle 数据库的话可以这样查询
代码
with t as (
select decode(b.列1,null,' ',b.列1) 列1,
c.列2,c.id
from c
inner join (
select distinct a.id
from a
left join(
select distinct b.id
from b
left join c
on b.id=c.id and b.列1=c.列2
where c.列2 is null
) d
on a.id=d.id
where d.id is null
) e
on c.id=e.id
left join b
on c.id=b.id and b.列1=c.列2
)
select id,ltrim(max(sys_connect_by_path(列1,',')),',') 列1,
ltrim(max(sys_connect_by_path(列2,',')),',') 列2
from (
select id,列1,列2,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from t
)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;
