现在有两个表,1个user表,一个审核表(checkList),user表中有id,name两列,而checklist表中有chk1id,chk1date,chk2id,chk2date,chk3id,chk3date,chk4id,chk4date,chk5id,chk5date,其中chk1id,chk2id,chk3id,chk4id,chk5id为user表中对应的id,其中checklist表每一行的chkid几列中每一列的chkid并不相同,现在想查找出类似如下格式的数据,chk1id,chk1name,chk1date,chk2id,chk2name,chk2date,chk3id,chk3name,chk3date,chk4id,chk4name,chk4date,chk5id,chk5name,chk5date,应该如何写这个sql啊,知道的朋友帮下忙吧,谢谢了!
from ckecklist a
join user b1 on a.chk1id=b1.id
join user b2 on a.chk2id=b2.id
join user b3 on a.chk3id=b3.id
join user b4 on a.chk4id=b4.id
join user b5 on a.chk5id=b5.id
from ckecklist a
join user b1 on a.chk1id=b1.id
join user b2 on a.chk2id=b2.id
join user b3 on a.chk3id=b3.id
join user b4 on a.chk4id=b4.id
join user b5 on a.chk5id=b5.id
join
left join
right join
full join
几个小时就会了,这样的题也好解了,联机帮助上有例子和介绍的.
select user.id ,user.name,checkList.data from user union checkList on user.id=checkList.id
如果变化存在,比如说会出现chk6之类的情况,那么这样的结构扩展性就不够了.
能不能改善一下风气.
要实现你的要求,查询速度会非常慢
select chk1id,
(select name from user where id = checklist.chk1id) as chk1name,
chk1date,
chk2id,
(select name from user where id = checklist.chk2id) as chk2name,
chk2date,
chk3id,
(select name from user where id = checklist.chk3id) as chk3name,
chk3date,
chk4id,
(select name from user where id = checklist.chk4id) as chk4name,
chk4date,
chk5id,
(select name from user where id = checklist.chk5id) as chk5name,
chk5date
from chk1name