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语句该怎么写好呢?
A表中有userId 为:111,222,333 userName 为:aaa,bbb,ccc三个人
B表中有userId 为:111,222 userState为:1,1 两个人我现在想A表和B表关联查询所有数据,并且A表中的userId为333的数据也要显示出来,页面上状态显示为0,请问SQL语句该怎么写好呢?
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
----------------------------------
1 111 aaa 1
2 222 bbb 1
3 333 ccc 0
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
----------------------------------
1 111 aaa 1
2 222 bbb 1
3 333 ccc 0
4 444 ddd 1
5 555 eee 0
6 666 fff 1
--sql语句
SELECT T.USERID, T.USERNAME, NVL(D.USERSTATE, 0)
FROM TEST_A T, TEST_B D
WHERE T.USERID = D.USERID(+)
正解!直接使用外连接就可以实现了。
SELECT A.userId , A.userName , nvl(B.userState,0)
FROM A表 A , B表 B
WHERE A.userid = B.userid(+)