两张表关联查询
A表有 userId userName B 表有 userId,userState
假设
A表中有userId 为：111,222,333 userName 为：aaa,bbb,ccc三个人
B表中有userId 为：111,222 userState为：1,1 两个人
我现在想A表和B表关联查询所有数据，并且A表中的userId为333的数据也要显示出来，页面上状态显示为0，请问SQL语句该怎么写好呢？
------解决方案--------------------
nvl(userState,0)
A.userid = B.userid(+)
------解决方案--------------------
这样的效果可以么？
with A as
(
select '111,222,333' userId,'aaa,bbb,ccc' userName from dual
),B as
(
select '111,222' userId ,'1,1' userState from dual
)
select t1.userId,t1.userName,nvl(t2.userState,0) userState
from
(
select replace(regexp_substr(userId,'[^,]+',1,level),',',' ') userId,
replace(regexp_substr(userName,'[^,]+',1,level),',',' ') userName
from A
connect by level<=length(userId)-length(replace(userId,',',''))+1
) t1 left join
(
select replace(regexp_substr(userId,'[^,]+',1,level),',',' ') userId,
replace(regexp_substr(userState,'[^,]+',1,level),',',' ') userState
from B
connect by level<=length(userId)-length(replace(userId,',',''))+1
) t2 on t1.userId = t2.userId
userId userName userState
----------------------------------
1111aaa1
2222bbb1
3333ccc0
------解决方案--------------------
多行数据 加个去重...
with A as
(
select '111,222,333' userId,'aaa,bbb,ccc' userName from dual union all
select '444,555' userId,'ddd,eee' userName from dual union all
select '666' userId,'fff' userName from dual
),B as
(
select '111,222' userId ,'1,1' userState from dual union all
select '444,666' userId ,'1,1' userState from dual
)
select t1.userId,t1.userName,nvl(t2.userState,0) userState
from
(
select distinct replace(regexp_substr(userId,'[^,]+',1,level),',',' ') userId,
replace(regexp_substr(userName,'[^,]+',1,level),',',' ') userName
from A
connect by level<=length(userId)-length(replace(userId,',',''))+1
) t1 left join
(
select distinct replace(regexp_substr(userId,'[^,]+',1,level),',',' ') userId,
replace(regexp_substr(userState,'[^,]+',1,level),',',' ') userState
from B
connect by level<=length(userId)-length(replace(userId,',',''))+1
) t2 on t1.userId = t2.userId
order by t1.userId
userId userName userState
----------------------------------
1111aaa1
2222bbb1
3333ccc0
4444ddd1