问一个sql

Question

有两个表A和B是多对多的关系，引入了一个中间表AB，外键是AID和BID，现在我要通过AID取B表中对应的数据，这个sql怎么写比较好

Answer 1

select a.* , b.* from a, b, ab
where a.id = ab.aid and ab.bid = b.id

Answer 2

给出一个多对多的经典例子，学生（A） 与 课程（B），得分则为AB表 
SQL> select * from student;
 
       SID SNAME
---------- --------------------
         1 张三
         2 李四
         3 王武
 
SQL> select * from course;
 
       CID CNAME
---------- --------------------
        11 语文
        12 数学
        13 英语
 
SQL> select * from score;
 
      SCID        SID        CID      SCORE
---------- ---------- ---------- ----------
       111          1         11         80
       112          1         12         78
       113          1         13         90
       114          2         11         82
       115          2         12         66
       116          2         13         92
       117          2         11         78
       118          2         12         84
       119          2         13         80
 
9 rows selectedSQL> SELECT S.SID, S.SNAME, C.CNAME, SC.SCORE
  2    FROM STUDENT S, --学生表
  3         COURSE  C， --课程表
  4          SCORE SC --得分表
  5   WHERE S.SID = SC.SID
  6     AND SC.CID = C.CID;
 
       SID SNAME                CNAME                     SCORE
---------- -------------------- -------------------- ----------
         2 李四                 语文                         78
         2 李四                 语文                         82
         1 张三                 语文                         80
         2 李四                 数学                         84
         2 李四                 数学                         66
         1 张三                 数学                         78
         2 李四                 英语                         80
         2 李四                 英语                         92
         1 张三                 英语                         90
 
9 rows selected
 
 
SQL> 

Answer 3

给出一个多对多的经典例子，学生（A） 与 课程（B），得分则为AB表 
SQL> select * from student;
 
       SID SNAME
---------- --------------------
         1 张三
         2 李四
         3 王武
 
SQL> select * from course;
 
       CID CNAME
---------- --------------------
        11 语文
        12 数学
        13 英语
 
SQL> select * from score;
 
      SCID        SID        CID      SCORE
---------- ---------- ---------- ----------
       111          1         11         80
       112          1         12         78
       113          1         13         90
       114          2         11         82
       115          2         12         66
       116          2         13         92
       117          2         11         78
       118          2         12         84
       119          2         13         80
 
9 rows selectedSQL> SELECT S.SID, S.SNAME, C.CNAME, SC.SCORE
  2    FROM STUDENT S, --学生表
  3         COURSE  C， --课程表
  4          SCORE SC --得分表
  5   WHERE S.SID = SC.SID
  6     AND SC.CID = C.CID;
 
       SID SNAME                CNAME                     SCORE
---------- -------------------- -------------------- ----------
         2 李四                 语文                         78
         2 李四                 语文                         82
         1 张三                 语文                         80
         2 李四                 数学                         84
         2 李四                 数学                         66
         1 张三                 数学                         78
         2 李四                 英语                         80
         2 李四                 英语                         92
         1 张三                 英语                         90
 
9 rows selected
 
 
SQL> 

Answer 4

SCORE表中的测试数据有点问题，适当修改SQL> select * from score;
 
      SCID        SID        CID      SCORE
---------- ---------- ---------- ----------
       111          1         11         80
       112          1         12         78
       113          1         13         90
       114          2         11         82
       115          2         12         66
       116          2         13         92
       117          3         11         78
       118          3         12         84
       119          3         13         80
 
9 rows selected
 
SQL> 
SQL> SELECT S.SID, S.SNAME, C.CNAME, SC.SCORE
  2    FROM STUDENT S, --学生表
  3         COURSE  C， --课程表
  4          SCORE SC --得分表
  5   WHERE S.SID = SC.SID
  6     AND SC.CID = C.CID
  7     ORDER BY 1,3;
 
       SID SNAME                CNAME                     SCORE
---------- -------------------- -------------------- ----------
         1 张三                 数学                         78
         1 张三                 英语                         90
         1 张三                 语文                         80
         2 李四                 数学                         66
         2 李四                 英语                         92
         2 李四                 语文                         82
         3 王武                 数学                         84
         3 王武                 英语                         80
         3 王武                 语文                         78

Answer 5

以订单表order和货物表product 和订单货物中间表order_product   为例：
已知中间表order ID : 6, 7  查出订单对应的货物信息
select * from product  p where p.id in (6,7)
/////////////////////////////////////////////
附：联合查询order & product 信息
select o.id,o.name,p.id,p.name from order  o ,product  p ,order_product  op where  o.id = op.oid and p.id=op.pid;