select t1.sid,t1.name,t2.fname,t4.wname,t3.mname,t5.wname from student t1,father t2,mother t3,workplace t4,workplace t5 where t1.fid=t2.fid and t1.mid=t3.mid and t2.wid=t4.wid and t3.wid=t5.wid
mysql> select * from student; +------+-----------+------+------+ | sid | sname | fid | mid | +------+-----------+------+------+ | 1 | XiaoQiang | 1 | 1 | +------+-----------+------+------+ 1 row in set (0.00 sec)mysql> select * from father; +------+------------+------+ | fid | fname | wid | +------+------------+------+ | 1 | XiaoQiangB | 1 | +------+------------+------+ 1 row in set (0.00 sec)mysql> select * from mother; +------+------------+------+ | mid | mname | wid | +------+------------+------+ | 1 | XiaoQiangM | 2 | +------+------------+------+ 1 row in set (0.00 sec)mysql> select * from workplace; +------+-------+ | wid | wname | +------+-------+ | 1 | GSJ | | 2 | DSJ | +------+-------+ 2 rows in set (0.00 sec)mysql> select sid,sname,fname,group_concat(if(f.wid=w.wid,wname,'')) as wname,mn ame,group_concat(if(m.wid=w.wid,wname,null)) as wname from student s,father f,mo ther m,workplace w where s.fid=f.fid and s.mid=m.mid group by s.sid; +------+-----------+------------+-------+------------+-------+ | sid | sname | fname | wname | mname | wname | +------+-----------+------------+-------+------------+-------+ | 1 | XiaoQiang | XiaoQiangB | GSJ, | XiaoQiangM | DSJ | +------+-----------+------------+-------+------------+-------+ 1 row in set (0.00 sec)
select s.sid,s.sname,f.fname,w1.wname,m.fname,w2.wname from student s ,father f, mother m, workplace w1,workplace w2 where s.fid=f.fid and s.mid=m.mid and f.wid=w1.wid and m.wid=w2.wid
select t1.sid,t1.name,t2.fname,t4.wname,t3.mname,t5.wname
from student t1,father t2,mother t3,workplace t4,workplace t5
where t1.fid=t2.fid and t1.mid=t3.mid and t2.wid=t4.wid and t3.wid=t5.wid
+------+-----------+------+------+
| sid | sname | fid | mid |
+------+-----------+------+------+
| 1 | XiaoQiang | 1 | 1 |
+------+-----------+------+------+
1 row in set (0.00 sec)mysql> select * from father;
+------+------------+------+
| fid | fname | wid |
+------+------------+------+
| 1 | XiaoQiangB | 1 |
+------+------------+------+
1 row in set (0.00 sec)mysql> select * from mother;
+------+------------+------+
| mid | mname | wid |
+------+------------+------+
| 1 | XiaoQiangM | 2 |
+------+------------+------+
1 row in set (0.00 sec)mysql> select * from workplace;
+------+-------+
| wid | wname |
+------+-------+
| 1 | GSJ |
| 2 | DSJ |
+------+-------+
2 rows in set (0.00 sec)mysql> select sid,sname,fname,group_concat(if(f.wid=w.wid,wname,'')) as wname,mn
ame,group_concat(if(m.wid=w.wid,wname,null)) as wname from student s,father f,mo
ther m,workplace w where s.fid=f.fid and s.mid=m.mid group by s.sid;
+------+-----------+------------+-------+------------+-------+
| sid | sname | fname | wname | mname | wname |
+------+-----------+------------+-------+------------+-------+
| 1 | XiaoQiang | XiaoQiangB | GSJ, | XiaoQiangM | DSJ |
+------+-----------+------------+-------+------------+-------+
1 row in set (0.00 sec)
from student s ,father f, mother m, workplace w1,workplace w2
where s.fid=f.fid and s.mid=m.mid and f.wid=w1.wid and m.wid=w2.wid