select
b.username as username1,
c.username as username2,
d.username as username3,
e.username as username4
from a
left join b on a.userid1=b.userid
left join b c on a.userid2=c.userid
left join b d on a.userid3=d.userid
left join b e on a.userid4=e.userid
b.username as username1,
c.username as username2,
d.username as username3,
e.username as username4
from a
left join b on a.userid1=b.userid
left join b c on a.userid2=c.userid
left join b d on a.userid3=d.userid
left join b e on a.userid4=e.userid
from a
left join b b1
on a.userid1=b1.userid
left join b b2
on a.userid1=b2.userid
left join b b3
on a.userid1=b3.userid
left join b b4
on a.userid1=b4.userid
select a.username1,b.username2,c.username3,d.username4 from a,b,c where a.userid1=b.userid
and a.userid2=c.userid and a.userid3=d.userid
方法二:
select a.username1,b.username2.c.username3,d.username4 from a
left join a on a.userid1=b.userid
left join b on a.userid2=c.userid
left join c on a.userid2=c.userid
left join d on a.userid3=d.userid
为什么LZ要这种表呢。不理解
b.username as username1,
c.username as username2,
d.username as username3,
e.username as username4
from a
left join b on a.userid1=b.userid
left join b c on a.userid2=c.userid
left join b d on a.userid3=d.userid
left join b e on a.userid4=e.userid语句没有办法优化了。优化你的表结构吧。在连接的字段上建立索引
create view t
as
select userid1 as id from a
union
select userid2 as id from a
union
select userid3 as id from a
union
select userid4 as id from a
go
select username into c from t,b where t.id=b.userid这样呢
对B表userid建立索引.--方法一:
select b1.username username1,
b2.username username2,
b3.username username3,
b4.username username4
from a
left join b b1 on a.userid1 = b1.userid
left join b b2 on a.userid1 = b2.userid
left join b b3 on a.userid1 = b3.userid
left join b b4 on a.userid1 = b4.userid--方法二:
select
(select username from b where userid = a.userid1) username1,
(select username from b where userid = a.userid2) username2,
(select username from b where userid = a.userid3) username3,
(select username from b where userid = a.userid4) username4
from a