mysql使用手册_MYSQL使用手册

3.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 = tp.id AND p1.tvab = tp.tvab AND

p2.id = p1.id AND p2.ptvab = p1.tvab AND

/* Just the screening survey */

tp.survey_no = 5 AND

/* Skip if partner died before 65 but allow emigration (dead=9) */

(p2.dead = 0 OR p2.dead = 9 OR

(p2.dead = 1 AND

(p2.death_date = 0 OR

(((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)

>= 65))))

AND

(

/* Twin is suspect */

(td.future_contact = 'Yes' AND td.suspect = 2) OR

/* Twin is suspect - Informant is Blessed */

(td.future_contact = 'Yes' AND td.suspect = 1

AND id.suspect = 1) OR

/* No twin - Informant is Blessed */

(ISNULL(td.suspect) AND id.suspect = 1

AND id.future_contact = 'Yes') OR

/* Twin broken off - Informant is Blessed */

(td.participation = 'Aborted'

AND id.suspect = 1 AND id.future_contact = 'Yes') OR

/* Twin broken off - No inform - Have partner */

(td.participation = 'Aborted' AND ISNULL(id.suspect)

AND p2.dead = 0))

AND

l.event = 'Finished'

/* Get at area code */

AND SUBSTRING(p1.postal_code, 1, 2) = pg.code

/* Not already distributed */

AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)

/* Has not refused or been aborted */

AND NOT (h.status = 'Refused' OR h.status = 'Aborted'

OR h.status = 'Died' OR h.status = 'Other')

ORDER BY

tvid;

一些解释：

·         CONCAT(p1.id, p1.tvab) + 0 AS tvid

我们想要在id和tvab的连接上以数字顺序排序。结果加0使得MySQL把结果变为一个数字。

·         列id

这标识一对孪生。它是所有表中的一个键。

·         列tvab

这标识孪生中的一个。它的值为1或2。

·         列ptvab

这是tvab的一个逆运算。当tvab是1，它是2，反之亦然。它用来保存输入并且使MySQL的优化查询更容易。

这个查询表明，怎样用联结(p1和p2)从同一个表中查找表。在例子中，这被用来检查孪生的一个是否在65岁前死了。如果如此，行不返回值。

上述所有孪生信息存在于所有表中。我们对id,tvab(所有表)和id,ptvab (person_data) 上采用键以使查询更快。

在我们的生产机器上(一台200MHz UltraSPARC)，这个查询返回大约 150-200 行并且时间不超过一秒。

表

行数

person_data

71074

lentus

5291

twin_project

5286

twin_data

2012

informant_data

663

harmony

381

postal_groups

100