MySQL学习(九)

1 一道面试题
新建两张表

mysql> create table m
    -> (
    -> mid int,
    -> hid int,
    -> gid int,
    -> mres varchar(10),
    -> matime date
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.79 sec)

mysql> create table t
    -> (
    -> tid int,
    -> tname varchar(20)
    -> )engine myisam charset utf8;
Query OK, 0 rows affected, 1 warning (0.43 sec)

插入如下数据

要求：

思考:

1

mysql> select hid,mres,gid,matime from m;

2

mysql> select t.tname, mres,gid,matime from m
    -> left join t
    -> on
    -> m.hid = t.tid;

可以把

m left join t on m.hid = t.tid

这一部分再当成一个表来看

3 初步思路

mysql> select t.tname,mres,gid,matime from
    -> (m left join t on m.hid = t.tid) left join t
    -> on m.gid = t.tid;

报错

ERROR 1066 (42000): Not unique table/alias: 't'

4 修改
错误的原因是 m t t相连，两张t表，名字冲突，起个别名就可以解决

mysql> select t1.tname,mres,t2.tname,matime from
    -> (m left join t as t1 on m.hid = t1.tid) left join t
    -> as t2
    -> on m.gid = t2.tid;

5 进一步

mysql> select t1.tname,mres,t2.tname,matime from
    -> (m left join t as t1 on m.hid = t1.tid) left join t
    -> as t2
    -> on m.gid = t2.tid
    -> where
    -> matime
    -> between '2006-06-01' and '2006-07-01';