Table a: id(number) des(varchar)
1 a_des1
2 a_des2Table b: id(number) name(varchar)
1 b_name1
SQL: select b.name, a.des
from a, b
where a.id = b.id(+)结果: b.name a.des
b_name1 _des1
a_des2
现:
Table a: id(varchar) des(varchar)
1,2 a_des1
2,3 a_des2
3,4 a_des3Table b: id(number) name(varchar)
1 b_name1
2 b_name2
3 b_name3想得到结果: b.name a.des
b_name1,b_name2 a_des1
b_name2,b_name3 a_des2
b_name3 a_des3意即原先a.id与b.id是一对一,现在a.id可以包含多个b.id
请问SQL如何写以达到原先左连接的效果?
谢谢!
1 a_des1
2 a_des2Table b: id(number) name(varchar)
1 b_name1
SQL: select b.name, a.des
from a, b
where a.id = b.id(+)结果: b.name a.des
b_name1 _des1
a_des2
现:
Table a: id(varchar) des(varchar)
1,2 a_des1
2,3 a_des2
3,4 a_des3Table b: id(number) name(varchar)
1 b_name1
2 b_name2
3 b_name3想得到结果: b.name a.des
b_name1,b_name2 a_des1
b_name2,b_name3 a_des2
b_name3 a_des3意即原先a.id与b.id是一对一,现在a.id可以包含多个b.id
请问SQL如何写以达到原先左连接的效果?
谢谢!
where instr(','||a.id||',',','||b.id||',')>0
group by a.des
添个(+)
select wm_concat(b.name),a.des from a,b
where instr(','||a.id||',',','||b.id(+)||',')>0
group by a.des
select tmp.id, wmsys.wm_concat(b.name) des from (select a.id, a.desc, b.name from a, b where instr(a.id||',', b.id||',', 1, 1)>0) tmp group by tmp.id, tmp.desc
select b.id,a.des from
(select b.id||','||lead(b.id) over(partition by 1 order by id) id ,b.name from b),a
where instr(','||a.id||',',','||b.id||',')>0
select b.id,a.des from
(select b.id||','||lead(b.id) over(partition by 1 order by id) id ,b.name from b),a
where instr(','||a.id||',',','||b.id(+)||',')>0
写oracle语句总是觉得力不从心,请问如何学习复杂的写法呢?
比如说可以参看些什么资料?