用
select Username,(select cssm from b where cslx='YHLX' and csz=a.YHLX) yhlx,(select cssm from b where cslx='YHXB' and csz=a.YHXB) yhxb,(select cssm from b where cslx='YHJB' and csz=a.YHJB) yhjb from a;
select Username,(select cssm from b where cslx='YHLX' and csz=a.YHLX) yhlx,(select cssm from b where cslx='YHXB' and csz=a.YHXB) yhxb,(select cssm from b where cslx='YHJB' and csz=a.YHJB) yhjb from a;
^_^
/
SQL> select * from a;USERNAME YHLX YHXB YHJB
---------- ---------- ---------- ----------
KKK 1 1 1
MMM 2 2 3
MKK 1 1 1
SQL> select * from b;CSLX CSZ CSSM
---------- ---------- ----------
YHLX 1 个人用户
YHLX 2 单位用户
YHXB 1 男
YHXB 2 女
YHXB 3
YHJB 1 普通
YHJB 2 高级
YHJB 3 管理员已选择8行。SQL> select Username,(select cssm from b where cslx='YHLX' and csz=a.YHLX) yhlx,(select cssm from b
where cslx='YHXB' and csz=a.YHXB) yhxb,(select cssm from b where cslx='YHJB' and csz=a.YHJB) yhjb fr
om a;USERNAME YHLX YHXB YHJB
---------- ---------- ---------- ----------
KKK 个人用户 男 普通
MMM 单位用户 女 管理员
MKK 个人用户 男 普通
如:
SQL> select * from a;USERNAME YH YH YH
---------- -- -- --
KKK 1 1 1
MMM 2 2 3
KKK 1 2 3SQL> select * from b
2 ;CSLX CS CSSM
---- -- ----------
YHLX 1 GRYH
YHLX 2 DWYH
YHXB 1 MAN
YHXB 2 WOMAN
YHJB 1 PTYH
YHJB 2 ADV
YHJB 3 MANAGER7 rows selected.SQL> select distinct a.username,b1.yhlx,b2.yhxb,b3.yhjb from a,
2 (select a.username,b.cssm yhlx from a, b where b.cslx = 'YHLX'
3 and b.csz=a.yhlx) b1,
4 (select a.username,b.cssm yhxb from a, b where b.cslx = 'YHXB'
5 and b.csz=a.yhxb) b2,
6 (select a.username,b.cssm yhjb from a, b where b.cslx = 'YHJB'
7 and b.csz=a.yhjb) b3
8 where
9 a.username = b1.username and a.username= b2.username and a.username=b3.username;USERNAME YHLX YHXB YHJB
---------- ---------- ---------- ----------
KKK GRYH MAN MANAGER
KKK GRYH MAN PTYH
KKK GRYH WOMAN MANAGER
KKK GRYH WOMAN PTYH
MMM DWYH WOMAN MANAGERSQL>
SQL>
SQL> select Username,
2 (select cssm from b where cslx='YHLX' and csz=a.YHLX) yhlx,
3 (select cssm from b where cslx='YHXB' and csz=a.YHXB) yhxb,
4 (select cssm from b where cslx='YHJB' and csz=a.YHJB) yhjb
5 from a;
(select cssm from b where cslx='YHLX' and csz=a.YHLX) yhlx,
*
ERROR at line 2:
ORA-00936: missing expression
SQL>