这样的2个表联合查询的sql语句如何写啊？（急）谢谢大家了！

[woaitiyu]

现在有两个表，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啊，知道的朋友帮下忙吧，谢谢了！

[huangyangweiyue]

select u.id,u.name,check.chkdate
from user u join checkList check on i.id = check.chkid

[huangyangweiyue]

如果我理解你的意思没错，你可以结贴了。

[huangyangweiyue]

select u.id,u.name,check.chkdate
from user u join checkList check on u.id = check.chkid

下面写错了

[arthas_sh]

多表查询,或者左连接都可以.真的没看懂LZ写的东西.

[huangyangweiyue]

select u.id,u.name,check.chkdate
from user u join checkList check on u.id = check.chkid

楼主急了半天 不知道行不行了撒。。。

[akun1982]

select u.id ,u.name,check.chkdate
from user u ,checkList where u.id = check.chkid

[forevertraveller]

看的不是忒明白, 可以试试这个:
select check.id,u.name,check.chkdate
from checkList check left join user u on u.id = check.chkid

[woaitiyu]

也许我没说清楚！现画了个图如下：

详细如下图：

user表：
id name
001 admin
002 abc
003 maike
004 tom

checkList表

chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date
001 2008-09-01 002 2008-09-01 002 2008-09-01 003 2008-09-01 001 2008-09-01
001 2008-09-01 003 2008-09-01 001 2008-09-01 003 2008-09-01 003 2008-09-01
003 2008-09-01 001 2008-09-01 002 2008-09-01 001 2008-09-01 002 2008-09-01
002 2008-09-01 002 2008-09-01 004 2008-09-01 003 2008-09-01 001 2008-09-01

我想要的结果

chk1id chk1name chk1date chk2id chk2name chk2date chk3id chk3name chk3date chk4id chk4name chk4date
001 admin 2008-09-01 002 abc 2008-09-01 002 abc 2008-09-01 003 maike 2008-09-01
001 admin 2008-09-01 003 maike 2008-09-01 001 admin 2008-09-01 003 maike 2008-09-01
003 maike 2008-09-01 001 admin 2008-09-01 002 abc 2008-09-01 001 admin 2008-09-01
002 abc 2008-09-01 002 abc 2008-09-01 004 tom 2008-09-01 003 maike 2008-09-01

[woaitiyu]

也许我没说清楚！现画了个图如下：

详细如下图：

user表：
id name
001 admin
002 abc
003 maike
004 tom

checkList表
chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date
001 2008-09-01 002 2008-09-01 002 2008-09-01 003 2008-09-01 001 2008-09-01
001 2008-09-01 003 2008-09-01 001 2008-09-01 003 2008-09-01 003 2008-09-01
003 2008-09-01 001 2008-09-01 002 2008-09-01 001 2008-09-01 002 2008-09-01
002 2008-09-01 002 2008-09-01 004 2008-09-01 003 2008-09-01 001 2008-09-01

我想要的结果
chk1id chk1name chk1date chk2id chk2name chk2date chk3id chk3name chk3date chk4id chk4name k4date
001 admin 2008-09-01 002 abc 2008-09-01 002 abc 2008-09-01 003 maike 2008-09-01
001 admin 2008-09-01 003 maike 2008-09-01 001 admin 2008-09-01 003 maike 2008-09-01
003 maike 2008-09-01 001 admin 2008-09-01 002 abc 2008-09-01 001 admin 2008-09-01
002 abc 2008-09-01 002 abc 2008-09-01 004 tom 2008-09-01 003 maike 2008-09-01

[forevertraveller]

明白了。应该是这个：
select check.id,u.name,check.chkdate
from checkList check left join user u on u.id = check.chkid

[huangyangweiyue]

chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date

这些是checkList表的字段名么？

[woaitiyu]

[Quote=引用 12 楼 huangyangweiyue 的回复:]
chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date

这些是checkList表的字段名么？
[/Quote]

是的！

[woaitiyu]

详细如下图：

user表：
id name
001 admin
002 abc
003 maike
004 tom

checkList表

chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date
001 2008-09-01 002 2008-09-01 002 2008-09-01 003 2008-09-01 001 2008-09-01
001 2008-09-01 003 2008-09-01 001 2008-09-01 003 2008-09-01 003 2008-09-01
003 2008-09-01 001 2008-09-01 002 2008-09-01 001 2008-09-01 002 2008-09-01
002 2008-09-01 002 2008-09-01 004 2008-09-01 003 2008-09-01 001 2008-09-01

我想要的结果

chk1id chk1name chk1date chk2id chk2name chk2date chk3id chk3name chk3date chk4id chk4name chk4date
001 admin 2008-09-01 002 abc 2008-09-01 002 abc 2008-09-01 003 maike 2008-09-01
001 admin 2008-09-01 003 maike 2008-09-01 001 admin 2008-09-01 003 maike 2008-09-01
003 maike 2008-09-01 001 admin 2008-09-01 002 abc 2008-09-01 001 admin 2008-09-01
002 abc 2008-09-01 002 abc 2008-09-01 004 tom 2008-09-01 003 maike 2008-09-01

[huangyangweiyue]

这样的表结构不能动了？
哪有这样的表结构。。。。

你做一个 chkid 和 chkdate

不就可以了。。。

[forevertraveller]

chk1id chk1date chk2id chk2date chk3id chk3date chk4id chk4date chk5id chk5date
都是字段名啊

试试这个
select check.chk1id,(select name from user where user.id=check.chk1id) check.name1,check.chkdate1
from checkList check

[huangyangweiyue]

你的checkList 没有一个ID号 和 user表对应么？
试试下面的不行， 你发到sql专区吧。。。。
希望有人能帮你解决这个问题。

select * //主要你那个chkdate那几个不知道怎么搞。 
from user u left join checkList ch on u.id in(ch.chk1id,ch.chk2id,ch.chk3id,ch.chk4id,ch.chk5id)

[forevertraveller]

或者干脆多left join 几次

select check.id1,u1.name,check.chkdate1, check.id2,u2.name,check.chkdate2, ...
from checkList check left join user u1 on u1.id = check.chkid1
left join user u2 on u2.id = check.chkid2
...

效果应当和上面是一样的

[~青峰~]

不知道对不对，楼主看看

Select chk1.chk1id,chk1.chk1name,chk1.chk1date,chk2.chk2id,chk2.chk2name,chk2.chk2date from 
(select rownum as rid,a.chk1id,b.name as chk1name,a.chk1date from tmp_chklist a left join tmp_user b on a.chk1id=b.id) chk1
inner join 
(select rownum as rid,a.chk2id,b.name as chk2name,a.chk2date from tmp_chklist a left join tmp_user b on a.chk2id=b.id) chk2
on chk1.rid=chk2.rid

[richard_2010]

LZ的表名命名太搞了,实在是看不下去.

[woaitiyu]

在别的朋友的帮助下解决了！谢谢楼上的朋友！