MySQL表联结自身

MySQL表联结自身

MySQL数据库不仅可以在表与表之间联结，而且还能让其自身联结，这个功能不能不说是很让人舒服的了。下面我们就来看看在mysql中怎么让表联结自身。
  
      假如在MySQL中创建一个表
       create table d
                    (
                     id int(10) auto_increment primary key not null,
                     name char(20) not null,
                     sex varchar(10) not null,
                     birth date default null
                   )type=myisam default charset=gbk;

 现在我们插入一些数据到d表中
                        INSERT INTO `d` (`id`, `name`, `sex`, `birth`) VALUES
                                                  (1, 'Gwen', 'm', '1989-03-17'),
                                                  (2, 'Harold', 'f', '1989-05-13'),
                                                  (3, 'Fang', 'm', '1990-08-27'),
                                                  (4, 'iane', 'm', '1979-08-31'),
                                                  (5, 'Gwens', 'f', '1988-09-11'),
                                                  (6, 'wen', 'f', '1988-08-03');

    下面我们要在d表中找出异性且年龄差距不超过4岁，这里就要用到表联结了：
      具体语句如下：
      select p1.name,p2.name,p1.sex,p2.sex,p1.birth,p2.birth from d as p1,d as p2 where p1.sex='f' and p2.sex='m' and (((year(p1.birth)-year(p2.birth))-(right(p1.birth,5)<right(p2.birth,5)))<4 or ((year(p1.birth)-year(p2.birth))-(right(p1.birth,5)<right(p2.birth,5)))>-4);
   具体的语法可见mysql手册，里面讲得很详细。
得到的是： 

 
name name sex sex birth birth
Harold Gwen f m 1989-05-13 1989-03-17
Gwens Gwen f m 1988-09-11 1989-03-17
wen Gwen f m 1988-08-03 1989-03-17
Harold Fang f m 1989-05-13 1990-08-27
Gwens Fang f m 1988-09-11 1990-08-27
wen Fang f m 1988-08-03 1990-08-27
Harold iane f m 1989-05-13 1979-08-31
Gwens iane f m 1988-09-11 1979-08-31
wen iane f m 1988-08-03 1979-08-31