这个用法倒不是很难,只是今天又遇到了.
还是记录一下吧.
用户表,
create table user(
id int,
name varchar(10)
);
insert into user values
(1,'user1'),
(2,'user2'),
(3,'user3');
团队表
create table team(
id int,
name varchar(10)
);
insert into team values
(2,'team1'),
(4,'team2'),
(6,'team3');
排行表
create table rank(
rankid int,
type int,
score int
);
insert into rank values
(1,1,10),
(2,2,20),
(2,1,15),
(4,2,5),
(3,1,1),
(6,2,30);
当type==1的时候,关联用户表,
当type==2的时候,关联团队表.
需要用一个SQL查询出用户名称和团队名称.
select
a.rankid,a.type,a.score,
if(b.id is null,c.id,b.id) uid,
if(b.name is null,c.name,b.name) name
from rank a
left join user b on (a.rankid=b.id and a.type=1)
left join team c on (a.rankid=c.id and a.type=2);
用外连接获取数据,然后合并.
![](//img.blog.itpub.net/blog/attachment/201503/31/29254281_1427814254cO5i.png?x-oss-process=style/bb)
还是记录一下吧.
用户表,
create table user(
id int,
name varchar(10)
);
insert into user values
(1,'user1'),
(2,'user2'),
(3,'user3');
团队表
create table team(
id int,
name varchar(10)
);
insert into team values
(2,'team1'),
(4,'team2'),
(6,'team3');
排行表
create table rank(
rankid int,
type int,
score int
);
insert into rank values
(1,1,10),
(2,2,20),
(2,1,15),
(4,2,5),
(3,1,1),
(6,2,30);
当type==1的时候,关联用户表,
当type==2的时候,关联团队表.
需要用一个SQL查询出用户名称和团队名称.
select
a.rankid,a.type,a.score,
if(b.id is null,c.id,b.id) uid,
if(b.name is null,c.name,b.name) name
from rank a
left join user b on (a.rankid=b.id and a.type=1)
left join team c on (a.rankid=c.id and a.type=2);
用外连接获取数据,然后合并.
![](http://img.blog.itpub.net/blog/attachment/201503/31/29254281_1427814254cO5i.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1480835/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1480835/