在藍色小舖看到有人問一個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執行速度會比較快.
當資料量大的時候最好加上,現在三筆資料,就不加了.
