MySQL CASE的應用 part2

還是藍色小鋪的同樣的問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD20110215141046DIE.html
---------------------------------------
先建立Table跟測試資料
USE test;

CREATE TABLE IF NOT EXISTS xhome (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
sname VARCHAR(20),
contact VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS xpatient (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
meter_id INT,
name VARCHAR(10),
gender ENUM('M','F'),
age INT,
home INT NOT NULL
);

CREATE TABLE IF NOT EXISTS xrecords (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid INT NOT NULL,
datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
value INT NOT NULL);

CREATE TABLE IF NOT EXISTS xflag (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
rid INT NOT NULL,
flag INT);

INSERT INTO xhome VALUES
(1, "home1", "home1s", "home1contact");

INSERT INTO xpatient VALUES
(1,1,"John", "M", 30, 1),
(2,2,"Mary", "F", 28, 1);

INSERT INTO xrecords VALUES
(NULL, 1, NULL, 133),
(NULL, 1, NULL, 134),
(NULL, 1, NULL, 123),
(NULL, 2, NULL, 135),
(NULL, 2, NULL, 136);
-------------------------------------
然後先用這道SQL Command 模擬原本3個 Tables的狀況
SELECT r.*, p.name, h.name AS home
FROM xhome h INNER JOIN xpatient p
ON p.home = h.id
INNER JOIN xrecords r
ON p.id = r.uid
ORDER BY r.datetime DESC;

+----+-----+---------------------+-------+------+-------+
| id | uid | datetime            | value | name | home  |
+----+-----+---------------------+-------+------+-------+
|  1 |   1 | 2011-02-17 15:51:38 |   133 | John | home1 |
|  2 |   1 | 2011-02-17 15:51:38 |   134 | John | home1 |
|  3 |   1 | 2011-02-17 15:51:38 |   123 | John | home1 |
|  4 |   2 | 2011-02-17 15:51:38 |   135 | Mary | home1 |
|  5 |   2 | 2011-02-17 15:51:38 |   136 | Mary | home1 |
+----+-----+---------------------+-------+------+-------+
接下來輸入 xflag 的測試資料,
INSERT INTO xflag VALUES
(NULL, 1, 22),
(NULL, 2, 27),
(NULL, 4, 38);

再用這道SQL
SELECT r.*, p.name, h.name AS home,(CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM xhome h INNER JOIN xpatient p
ON p.home = h.id
INNER JOIN xrecords r
ON p.id = r.uid
LEFT JOIN xflag f
ON r.id = f.rid
ORDER BY r.datetime DESC;

+----+-----+---------------------+-------+------+-------+------+
| id | uid | datetime            | value | name | home  | flag |
+----+-----+---------------------+-------+------+-------+------+
|  5 |   2 | 2011-02-17 15:51:38 |   136 | Mary | home1 |    0 |
|  1 |   1 | 2011-02-17 15:51:38 |   133 | John | home1 |   22 |
|  2 |   1 | 2011-02-17 15:51:38 |   134 | John | home1 |   27 |
|  3 |   1 | 2011-02-17 15:51:38 |   123 | John | home1 |    0 |
|  4 |   2 | 2011-02-17 15:51:38 |   135 | Mary | home1 |   38 |
+----+-----+---------------------+-------+------+-------+------+