两张表关联查询,该怎么处理

两张表关联查询
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&lt;=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&lt;=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&lt;=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&lt;=length(userId)-length(replace(userId,',',''))+1
) t2 on t1.userId = t2.userId
order by t1.userId

   userId userName userState
----------------------------------
1111aaa1
2222bbb1
3333ccc0
4444ddd1 
 
5555eee0
6666fff1

[解决办法]
这需要用到左外连接以及nvl函数，具体的sql语句如下所示：



直接左连接就行了
[解决办法]
nvl函数，左连SELECT A.USERID,A.USERNAME,NVL(B.USERSTATE) FROM A LEFT JOIN B ON A.USERID = B.USERID

两张表关联查询,该怎么处理

热点推荐