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就不用了,因為都一樣,只是佔篇幅而已.