一条sql查询

Question

create table testa(
aid int,
name varchar2(20)
)create table testb(
bid int,
bname varchar2(20)
)create table c(
aid int,
bid int
)
insert into testa(1,'a1');
insert into testa(2,'a2');
insert into testb(1,'b1');
insert into testb(2,'b2');
insert into c(1,1);要显示aid，bid，aname，bname
如果c表中a，b没有关联显示ab无关系

Answer 1

上面写的有点问题。。如果c表中a，b没有关联 bname显示ab无关系

Answer 2

select aid，aname，bid，(select decode(count(*),0,'ab无关系',testb.bname) from c where aid=testa.aid and bid=testb.bid)) as bname
from testa,testb
试一下

Answer 3

--试一下：select a.aid, b.bid, a.aname, b.bname
  from testa a, test b, c
 where c.aid(+) = a.aid
    or c.aid(+) = b.bid;

Answer 4

不行哦。。未找到预期的关键字from...在，(select decode(count(*),0,'ab无关系',testb.bname) from c where aid=testa.aid and bid=testb.bid)) as bname 这里报错了

Answer 5

SELECT A.aid，B.bid，A.aname，B.bname  FROM A,B,C WHERE A.AID=C.AID AND B.BID=C.BID
UNION ALL
SELECT NULL,NULL,NULL,CASE WHEN (C.AID IS NULL AND C.BID IS NULL) THEN 'ab无关系' ELSE NULL END FROM C

Answer 6

搞定, 贴代码, 没优化过:
SQL> select * from testa;       AID NAME
---------- --------------------
         1 a1
         2 a2SQL> select * from testb;       BID BNAME
---------- --------------------
         1 b1
         2 b2SQL> select * from c;       AID        BID
---------- ----------
         1          1SQL> select f.aid, f.bid, f.aname, decode(f.caid, null, 'no relationship', f.bname ) bname from
  2  (
  3   select T.aid, T.bid, t.aname, T.bname, c.aid caid, c.bid cbid from
  4   ( select aid aid, bid bid, A.name aname, B.bname bname from testa A, testb B) T, c
  5   where T.aid = c.aid(+) 
  6   and   T.bid = c.bid(+)
  7  ) F;       AID        BID ANAME                BNAME
---------- ---------- -------------------- --------------------
         1          1 a1                   b1
         2          1 a2                   no relationship
         1          2 a1                   no relationship
         2          2 a2                   no relationship

Answer 7

or或者in中不允许外部连接字符（+）

Answer 8

没有写在or和in中, 不喜欢+, 改下不也很简单么
SQL> select f.aid, f.bid, f.aname, decode(f.caid, null, 'no relationship', f.bname ) bname from
  2  (
  3   select T.aid, T.bid, t.aname, T.bname, c.aid caid, c.bid cbid from
  4   ( select aid aid, bid bid, A.name aname, B.bname bname from testa A, testb B) T left join c
  5   on T.aid = c.aid 
  6   and T.bid = c.bid
  7  ) F;       AID        BID ANAME                BNAME
---------- ---------- -------------------- --------------------
         1          1 a1                   b1
         1          2 a1                   no relationship
         2          1 a2                   no relationship
         2          2 a2                   no relationship

Answer 9

 - -！只有aid 1.。没有2

Answer 10

select a.aid aid,
       b.bid bid,
       a.aname aname,
       decode(a.aid - b.bid, 0, b.bname, 'ab无关系') bname
  from testa a, test b, c
 where c.aid(+) = a.aid
    or c.aid(+) = b.bid;

Answer 11

SQL>  select t.aid, t.bid, t.name, decode(c.aid, null, 'ab无关系', t.bname) bname
  2     from (select * from testa full join testbb on testa.aid = testbb.bid) t,
  3          c
  4    where c.aid(+) = t.aid
  5      and c.bid(+) = t.bid
  6  /       AID        BID NAME                 BNAME
---------- ---------- -------------------- --------------------
         1          1 a1                   b1
         2          2 a2                   ab无关系SQL> 

Answer 12

-- TRY IT ..
SQL> SELECT * FROM TESTA;                                    AID NAME
--------------------------------------- --------------------
                                      1 a1
                                      2 a2SQL> SELECT * FROM TESTB;                                    BID BNAME
--------------------------------------- --------------------
                                      1 b1
                                      2 b2SQL> SELECT * FROM C;                                    AID                                     BID
--------------------------------------- ---------------------------------------
                                      3                                       3
                                      1                                       3
                                      1                                       1
                                      3                                       1SQL> SELECT A.AID,
  2         B.BID,
  3         NAME,
  4         DECODE(NVL(A.AID,0)*NVL(B.BID,0),0,'AB无关系',BNAME) BNAME
  5    FROM TESTA A,
  6         TESTB B,
  7         C
  8   WHERE C.AID = A.AID(+)
  9     AND C.BID = B.BID(+);                                    AID                                     BID NAME                 BNAME
--------------------------------------- --------------------------------------- -------------------- --------------------
                                                                              1                      AB无关系
                                      1                                       1 a1                   b1
                                                                                                     AB无关系
                                      1                                         a1                   AB无关系

Answer 13

这个aid和bid已经有关系了。。 1          2 a1                   no relationship
就不应该有了

Answer 14

-- 显式格式不好..
SQL> SELECT A.AID,
  2         B.BID,
  3         NAME,
  4         DECODE(NVL(A.AID,0)*NVL(B.BID,0),0,'AB无关系',BNAME) BNAME
  5    FROM TESTA A,
  6         TESTB B,
  7         C
  8   WHERE C.AID = A.AID(+)
  9     AND C.BID = B.BID(+);                 AID                   BID     NAME                 BNAME
---------------------- --------------------- -------------------- --------------------
                                         1                          AB无关系
                   1                       1     a1                    b1
                                                                    AB无关系
                   1                             a1                   AB无关系

Answer 15

----------------------------------
显示结果大概要这个样子的。。
aid   name bid bname
1       a1  1   b1
2       a2  ab没关系    ab没关系

Answer 16

日！你自己到底要什么．．．上面的代码自己弄个来改改就行．
SQL> select t.aid,t.name,decode(c.aid, null, 'ab无关系', t.bid) bid,decode(c.aid, null, 'ab无关系', t.bname) bname
  2  from (select * from testa full join testbb on testa.aid = testbb.bid) t,
  3  c
  4  where c.aid(+) = t.aid
  5  and c.bid(+) = t.bid
  6  /       AID NAME                 BID                                      BNAME
---------- -------------------- ---------------------------------------- --------------------
         1 a1                   1                                        b1
         2 a2                   ab无关系                                 ab无关系SQL> 

Answer 17

SELECT A.AID,A.NAME,B.BID,B.BNAME FROM TESTA A,TESTB B,TESTC C
WHERE A.AID IN (SELECT AID FROM TESTC WHERE AID=A.AID AND BID=B.BID)