## 求助，多对一关联（多列对一列），如何写查询代码，谢谢！

pargy 发布于 2015/09/10 10:47

A表
banji yuwen shuxue

B表
laoshixuehao   xingming nianling
100001 张三 25
100002 李四 45
100003 王五 28
100004 赵六 50
100005 吴七 40
100006 刘八 48

banji 语文老师姓名 年龄 数学老师姓名 年龄

 使用了 如下，结果不行。求助 select ***** from  A as yu,a as shu,b where yu.yuwen = b.laoshixuehao or shu.shuxue = b.laoshixuehao

0

1. 使用联合查询，即连续两次join老师表：

2. 使用子查询，即先join一次老师表，找出语文老师的年龄和姓名，在以同样的方式找数学老师的年龄和姓名：

1. 联合查询：

```select b.banji, y.xingming as yxingming, y.nianling as ynianling,
s.xingming as sxingming, s.nianling as snianling
from banji b join laoshi y on b.yuwen=y.xuehao
join laoshi s on b.shuxue=s.xuehao order by b.banji asc;
```

2. 子查询：

```select sub.banji, sub.yuwen, sub.ynianling,
l2.xingming as sxingming, l2.nianling as snianling from
(select b.banji, l.xingming as yuwen, l.nianling as ynianling, b.shuxue
from banji b join laoshi l on b.yuwen=l.xuehao) as sub
join laoshi as l2 on sub.shuxue=l2.xuehao order by sub.banji asc;
```
0

oracle写法：

select A表.banji,（select B表.姓名 from B表 where B表.laoshixuhao = A表.yuwen）

 语文老师姓名,

（select B表.姓名 from B表 where B表.laoshixuhao = A表.shuxue）

（select B表.nianling from B表 where B表.laoshixuhao = A表.yuwen）

,（select B表.nianling from B表 where B表.laoshixuhao = A表.shuxue）

 数学老师年龄  from A表;
p

0
p

#### 引用来自“这昵称牛逼”的评论

1. 使用联合查询，即连续两次join老师表：

2. 使用子查询，即先join一次老师表，找出语文老师的年龄和姓名，在以同样的方式找数学老师的年龄和姓名：

1. 联合查询：

```select b.banji, y.xingming as yxingming, y.nianling as ynianling,
s.xingming as sxingming, s.nianling as snianling
from banji b join laoshi y on b.yuwen=y.xuehao
join laoshi s on b.shuxue=s.xuehao order by b.banji asc;
```

2. 子查询：

```select sub.banji, sub.yuwen, sub.ynianling,
l2.xingming as sxingming, l2.nianling as snianling from
(select b.banji, l.xingming as yuwen, l.nianling as ynianling, b.shuxue
from banji b join laoshi l on b.yuwen=l.xuehao) as sub
join laoshi as l2 on sub.shuxue=l2.xuehao order by sub.banji asc;
```