在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就不用了,因為都一樣,只是佔篇幅而已.
2011年2月21日 星期一
2011年2月17日 星期四
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 |
+----+-----+---------------------+-------+------+-------+------+
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 |
+----+-----+---------------------+-------+------+-------+------+
2011年2月14日 星期一
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;
就可以得到你需要的了.
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;
就可以得到你需要的了.
2010年11月18日 星期四
最近Pidgin 登入MSN時 SSL憑證錯誤的解決方法
最近用Pidgin登入MSN會出現SSL憑證問題.
解決方法:
當然要先把Pidgin關掉.
download 新的授權憑證檔
here ===> http://files.andreineculau.com/projects/pidgin/omega.contacts.msn.com.txt
將 .txt 的副檔名拿掉, 存成 omega.contacts.msn.com 即可.
然後蓋掉原本在 ~/.purple/certificates/x509/tls_peers 目錄下的 omega.contacts.msn.com 檔案
然後再啟動Pidgin
解決方法:
當然要先把Pidgin關掉.
download 新的授權憑證檔
here ===> http://files.andreineculau.com/projects/pidgin/omega.contacts.msn.com.txt
將 .txt 的副檔名拿掉, 存成 omega.contacts.msn.com 即可.
然後蓋掉原本在 ~/.purple/certificates/x509/tls_peers 目錄下的 omega.contacts.msn.com 檔案
然後再啟動Pidgin
2010年10月6日 星期三
有意思的sql問題
在藍色小舖看到有人問一個sql問題.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD201010051423497Z5.html
我已經回答了.
use test;
CREATE TABLE `blue1` (
`data1` smallint(6) NOT NULL,
PRIMARY KEY (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `blue2` (
`q1` smallint(6) NOT NULL,
`q2` smallint(6) NOT NULL,
`q3` smallint(6) NOT NULL,
KEY `fk1` (`q1`),
KEY `fk2` (`q2`),
KEY `fk3` (`q3`),
CONSTRAINT `fk1` FOREIGN KEY (`q1`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk2` FOREIGN KEY (`q2`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk3` FOREIGN KEY (`q3`) REFERENCES `blue1` (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
insert into blue1(data1) values (1),(2),(3),(4),(5);
insert into blue2 values
(1,3,4),
(2,1,5),
(3,4,1);
root@[test]>select count(q1) into @total from blue2;
Query OK, 1 row affected (0.00 sec)
root@[test]>select @total;
+--------+
| @total |
+--------+
| 3 |
+--------+
root@[test]>select q1, count(q1)/@total
-> from blue2
-> group by q1
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q1 from blue2);
+----+------------------+
| q1 | count(q1)/@total |
+----+------------------+
| 1 | 0.3333 |
| 2 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+
root@[test]>select q2, count(q2)/@total
-> from blue2
-> group by q2
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q2 from blue2);
+----+------------------+
| q2 | count(q2)/@total |
+----+------------------+
| 1 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.3333 |
| 2 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+
root@[test]>select q3, count(q3)/@total
-> from blue2
-> group by q3
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q3 from blue2);
+----+------------------+
| q3 | count(q3)/@total |
+----+------------------+
| 1 | 0.3333 |
| 4 | 0.3333 |
| 5 | 0.3333 |
| 2 | 0.0000 |
| 3 | 0.0000 |
+----+------------------+
subquery 部份 select q3 from blue2 加上 distinct執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.
http://www.blueshop.com.tw/board/FUM20041006152746MYF/BRD201010051423497Z5.html
我已經回答了.
use test;
CREATE TABLE `blue1` (
`data1` smallint(6) NOT NULL,
PRIMARY KEY (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `blue2` (
`q1` smallint(6) NOT NULL,
`q2` smallint(6) NOT NULL,
`q3` smallint(6) NOT NULL,
KEY `fk1` (`q1`),
KEY `fk2` (`q2`),
KEY `fk3` (`q3`),
CONSTRAINT `fk1` FOREIGN KEY (`q1`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk2` FOREIGN KEY (`q2`) REFERENCES `blue1` (`data1`),
CONSTRAINT `fk3` FOREIGN KEY (`q3`) REFERENCES `blue1` (`data1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
insert into blue1(data1) values (1),(2),(3),(4),(5);
insert into blue2 values
(1,3,4),
(2,1,5),
(3,4,1);
root@[test]>select count(q1) into @total from blue2;
Query OK, 1 row affected (0.00 sec)
root@[test]>select @total;
+--------+
| @total |
+--------+
| 3 |
+--------+
root@[test]>select q1, count(q1)/@total
-> from blue2
-> group by q1
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q1 from blue2);
+----+------------------+
| q1 | count(q1)/@total |
+----+------------------+
| 1 | 0.3333 |
| 2 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+
root@[test]>select q2, count(q2)/@total
-> from blue2
-> group by q2
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q2 from blue2);
+----+------------------+
| q2 | count(q2)/@total |
+----+------------------+
| 1 | 0.3333 |
| 3 | 0.3333 |
| 4 | 0.3333 |
| 2 | 0.0000 |
| 5 | 0.0000 |
+----+------------------+
root@[test]>select q3, count(q3)/@total
-> from blue2
-> group by q3
-> union
-> select data1, 0/@total
-> from blue1 where data1 not in (select q3 from blue2);
+----+------------------+
| q3 | count(q3)/@total |
+----+------------------+
| 1 | 0.3333 |
| 4 | 0.3333 |
| 5 | 0.3333 |
| 2 | 0.0000 |
| 3 | 0.0000 |
+----+------------------+
subquery 部份 select q3 from blue2 加上 distinct執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.
2010年9月1日 星期三
MySQL Database Quota Control
之前在酷學園有人問到,所以後來就開發了這功能.
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar
詳細的資料整理成pdf了.
http://www.filesonic.com/file/17520903/mysqlquotabunko.rar
訂閱：
文章 (Atom)