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

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]