mysql面试题左链接右_mysql左连接查询面试题

[/img]

第一步：首先创建第一个表，并添加数据，如下图：

create table m(

mid int primary key auto_increment,

hid int,

gid int,

result varchar(50),

mtime date

);

insert into m

(hid,gid,result,mtime)

values

(1,2,'2:0','2006-05-21'),

(2,3,'1:2','2006-06-21'),

(3,1,'2:5','2006-05-25'),

(2,1,'3:2','2006-07-21');

[img]

[/img]

第二步：创建第二张表，并模拟添加数据，如下：

create table t(

tid int,

tname varchar(10)

);

insert into t

values

(1,'巴萨'),

(2,'皇马'),

(3,'野马队');

[img]

[/img]

第三步，先把输出的顺序搞定，如下：

select hid, result, gid,mtime from m;

[img]

[/img]

第四步：把主队的名字取出来，如下：

select hid,tname, result,gid,mtime

from

m left join t

on m.hid = t.tid;

[img]

[/img]

第五步：把客队的名字也取出来，如下：

select hid,t1.tname as '主队', result,gid,t2.tname as '客队',mtime

from

m left join t as t1

on m.hid = t1.tid

left join t as t2

on m.gid = t2.tid;

[img]

[/img]

第六步：再查询一次，这次不需要 hid 与 gid 这两列， ok let's go

select t1.tname as '主队',result as '比分',t2.tname as '客队',mtime as '比赛时间'

from

m left join t as t1

on m.hid = t1.tid

left join t as t2

on m.gid = t2.tid;

[img]

[/img]

第七步：添加查询条件：查询2006-06-01  到2006-07-01之间的比赛

select t1.tname as '主队',result as '比分',t2.tname as '客队',mtime as '比赛时间'

from

m left join t as t1

on m.hid = t1.tid

left join t as t2

on m.gid = t2.tid

where mtime between '2006-06-01' and '2006-07-01';

[img]

[/img]

over

大小: 2.6 KB

大小: 1.3 KB

大小: 3.1 KB

大小: 4.2 KB

大小: 6.2 KB

大小: 7.1 KB

大小: 5.9 KB

大小: 93.9 KB

0

顶

0

踩

分享到：

2013-02-28 13:20

浏览 1016

评论