MySQL的資料分組統計

在ITPub看到有人問MySQL的資料分組統計.
http://www.itpub.net/thread-1390472-1-2.html
表数据分组统计，SQL的写法请教？

有表格式如下：
ID        NO        SEL1        SEL2        SEL3
1        001        A        A        A
2        001        A        B        C
3        001        B        B        C
4        001        C        B        C
5        001        B        B        C
6        001        B        C        C
7        002        A        A        B
8        002        B        A        B
9        002        C        A        B
10        002        C        B        B
11        002        C        B        B
12        002        C        B        C

想统计出如下结果：
1、统计每个NO号（如001）的SEL1，SEL2，SEL3中各自包含A的个数，B的个数，C的个数；
2、统计每个NO号（如001）的SEL1，SEL2，SEL3中A、B、C分别占总数的比例。

我的解法:


CREATE TABLE x0221 (
id INT AUTO_INCREMENT PRIMARY KEY,
no CHAR(3) NOT NULL,
sel1 CHAR(1) NOT NULL,
sel2 CHAR(1) NOT NULL,
sel3 CHAR(1) NOT NULL);

INSERT INTO x0221(no, sel1, sel2, sel3) VALUES
("001", "A", "A", "A"),
("001", "A", "B", "C"),
("001", "B", "B", "C"),
("001", "C", "B", "C"),
("001", "B", "B", "C"),
("001", "B", "C", "C"),
("002", "A", "A", "B"),
("002", "B", "A", "B"),
("002", "C", "A", "B"),
("002", "C", "B", "B"),
("002", "C", "B", "B"),
("002", "C", "B", "C");

先做sel的ABC總數計算:
SELECT no,SUM(CASE WHEN sel1="A" THEN 1 ELSE 0 END) as sel1a,
           SUM(CASE WHEN sel1="B" THEN 1 ELSE 0 END) as sel1b,
           SUM(CASE WHEN sel1="C" THEN 1 ELSE 0 END) as sel1c
FROM x0221
GROUP BY no;
+-----+-------+-------+-------+
| no  | sel1a | sel1b | sel1c |
+-----+-------+-------+-------+
| 001 |     2 |     3 |     1 |
| 002 |     1 |     1 |     4 |
+-----+-------+-------+-------+
這是很標準的作法,利用CASE
那要算比例呢?大家都會用COUNT(1)來算總數,但是要把它用一道SQL呢?
其實我們可以用
SELECT no, sel1a, sel1a / b.cnt AS percent_sel1a,
            sel1b, sel1b / b.cnt AS percent_sel1b,
            sel1c, sel1c / b.cnt AS percent_sel1c
FROM (SELECT no,SUM(CASE WHEN sel1="A" THEN 1 ELSE 0 END) as sel1a,
           SUM(CASE WHEN sel1="B" THEN 1 ELSE 0 END) as sel1b,
           SUM(CASE WHEN sel1="C" THEN 1 ELSE 0 END) as sel1c
       FROM x0221
       GROUP BY no) a CROSS JOIN
      (SELECT COUNT(1) as cnt FROM x0221) b
GROUP BY no
ORDER BY no;
+-----+-------+---------------+-------+---------------+-------+---------------+
| no  | sel1a | percent_sel1a | sel1b | percent_sel1b | sel1c | percent_sel1c |
+-----+-------+---------------+-------+---------------+-------+---------------+
| 001 |     2 |        0.1667 |     3 |        0.2500 |     1 |        0.0833 |
| 002 |     1 |        0.0833 |     1 |        0.0833 |     4 |        0.3333 |
+-----+-------+---------------+-------+---------------+-------+---------------+
善用CROSS JOIN,讓總數的值都可以在每一個結果裡面引用.
sel2,sel3就不用了,因為都一樣,只是佔篇幅而已.

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 |
+----+-----+---------------------+-------+------+-------+------+

MySQL CASE的應用

今天有人在藍色小舖問了一個問題:
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD20110215141046DIE.html
-----------------
這是原本的Record Table
id uid datetime value
1 9 2009-08-31 18:15:29 133

我想在Record Table 加一欄,叫FLAG來記載標記標籤,最快捷的方法是在Record Table 直接加上去,但是因為之前PROGRAM問題,而這Windows PROGRAM己經在使用中,我並不能改動Record TABLE.
我就設計另一個TABLE來儲FLAG的內容,叫FLAG TABLE, 內容以下
id pid rid flag
1 34 400 1

因為不是每一個在RECORD的紀錄都有FLAG的,所以我想問QUERY是怎麼寫來連合這兩個TABLE,顯示以下格式?
id uid datetime value flag
1 9 2009-08-31 18:15:29 133 0

如果FLAG TABLE沒有紀錄,就變成0

我的表達不好,大家有不明白的地方,請告訴我,謝謝..
-------------------
我回答如下:
先建立測試用的Table與資料:

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

CREATE TABLE blue0215flag (
id  INT NOT NULL,
pid INT NOT NULL,
rid INT NOT NULL,
flag INT NOT NULL);

INSERT INTO blue0215 VALUES
(1,9,NULL,133),
(2,10,NULL,134),
(3,11,NULL,135),
(4,12,NULL,136);

INSERT INTO blue0215flag VALUES
(1,34,400,1),
(2,35,401,2);


使用如下SQL Command:
SELECT o.id,o.uid,o.datetime,o.value, (CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM blue0215 o LEFT JOIN blue0215flag f
ON o.id = f.id;

Result:
+----+-----+---------------------+-------+------+
| id | uid | datetime            | value | flag |
+----+-----+---------------------+-------+------+
|  1 |   9 | 2011-02-15 14:45:17 |   133 |    1 |
|  2 |  10 | 2011-02-15 14:45:17 |   134 |    2 |
|  3 |  11 | 2011-02-15 14:45:17 |   135 |    0 |
|  4 |  12 | 2011-02-15 14:45:17 |   136 |    0 |
+----+-----+---------------------+-------+------+

當blue0215flag Table有值時,flag 就取出,若沒有則show出 0

使用以下SQL Command產生一個view,會更方便使用.

CREATE VIEW vblue0215 AS
SELECT o.id,o.uid,o.datetime,o.value, (CASE WHEN f.flag THEN f.flag ELSE 0 END) AS flag
FROM blue0215 o LEFT JOIN blue0215flag f
ON o.id = f.id;

直接 SELECT * FROM vblue0215;
就可以得到你需要的了.