两张表关联查询
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,'[