MySQL中文参考手册--8.MySQL教程--8.7 双胞胎项目的查询（实例）

在Analytikerna 和 Lentus，我们为一个大的研究项目工程一直在做系统和现场工作。这个项目是Institute of Environmental Medicine at Karolinska Institutet Stockholm 和 the Section on Clinical Research in Aging and Psychology at the University of Southern California的合作项目。

双胞胎研究的更多信息可在下列链接找到：

http://www.imm.ki.se/TWIN/TWINUKW.HTM

项目的后面部分是用一个用Perl和MySQL编写的web接口来管理。

每天晚上所有会谈的数据被移入一个MySQL数据库。

8.7.1 找出所有非独处的双胞胎

下列查询用来决定谁进入项目的第二部分：

select
        concat(p1.id, p1.tvab) + 0 as tvid,
        concat(p1.christian_name, " ", p1.surname) as Name,
        p1.postal_code as Code,
        p1.city as City,
        pg.abrev as Area,
        if(td.participation = "Aborted", "A", " ") as A,
        p1.dead as dead1,
        l.event as event1,
        td.suspect as tsuspect1,
        id.suspect as isuspect1,
        td.severe as tsevere1,
        id.severe as isevere1,
        p2.dead as dead2,
        l2.event as event2,
        h2.nurse as nurse2,
        h2.doctor as doctor2,
        td2.suspect as tsuspect2,
        id2.suspect as isuspect2,
        td2.severe as tsevere2,
        id2.severe as isevere2,
        l.finish_date
from
        twin_project as tp
        /* For Twin 1 */
        left join twin_data as td on tp.id = td.id and tp.tvab = td.tvab
        left join informant_data as id on tp.id = id.id and tp.tvab = id.tvab
        left join harmony as h on tp.id = h.id and tp.tvab = h.tvab
        left join lentus as l on tp.id = l.id and tp.tvab = l.tvab
        /* For Twin 2 */
        left join twin_data as td2 on p2.id = td2.id and p2.tvab = td2.tvab
        left join informant_data as id2 on p2.id = id2.id and p2.tvab = id2.tvab
        left join harmony as h2 on p2.id = h2.id and p2.tvab = h2.tvab
        left join lentus as l2 on p2.id = l2.id and p2.tvab = l2.tvab,
        person_data as p1,
        person_data as p2,
        postal_groups as pg
where
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id =