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
你的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)
或者干脆多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
select u.id,u.name,check.chkdate
from user u join checkList check on u.id = check.chkid下面写错了
select u.id,u.name,check.chkdate
from user u join checkList check on u.id = check.chkid楼主急了半天 不知道行不行了撒
from user u ,checkList where u.id = check.chkid
select check.id,u.name,check.chkdate
from checkList check left join user u on u.id = check.chkid
id name
001 admin
002 abc
003 maike
004 tomcheckList表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
id name
001 admin
002 abc
003 maike
004 tomcheckList表
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
select check.id,u.name,check.chkdate
from checkList check left join user u on u.id = check.chkid
id name
001 admin
002 abc
003 maike
004 tomcheckList表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
哪有这样的表结构。你做一个 chkid 和 chkdate 不就可以了
都是字段名啊试试这个
select check.chk1id,(select name from user where user.id=check.chk1id) check.name1,check.chkdate1
from checkList check
试试下面的不行, 你发到sql专区吧。
希望有人能帮你解决这个问题。
select * //主要你那个chkdate那几个不知道怎么搞。
from user u left join checkList ch on u.id in(ch.chk1id,ch.chk2id,ch.chk3id,ch.chk4id,ch.chk5id)
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