測試數據如下：
表t1和t2通過t1_id關聯在一起；
t1中只有一條記錄的type為0，另外三條記錄的type為1；
對於t1中的每個t1_id, t2中都有6條記錄與其關聯。
CREATE TABLE `t1` (
`t1_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`type` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`t1_id`),
KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t2` (
`t2_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`t1_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`t2_id`),
KEY `t1_id` (`t1_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t1 SET TYPE=0;
INSERT INTO t1 SET TYPE=1;
INSERT INTO t1 SET TYPE=1;
INSERT INTO t1 SET TYPE=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=1;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=2;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=3;
INSERT INTO t2 SET t1_id=4;
INSERT INTO t2 SET t1_id=4;
INSERT INTO t2 SET t1_id=4;
INSERT INTO t2 SET t1_id=4;
INSERT INTO t2 SET t1_id=4;
INSERT INTO t2 SET t1_id=4;
mysql> SELECT * FROM t1;
+-------+------+
| t1_id | TYPE |
+-------+------+
| 1 | 0 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+-------+------+
4 ROWS IN SET (0.00 sec)
mysql> SELECT * FROM t2;
+-------+-------+
| t2_id | t1_id |
+-------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
| 11 | 2 |
| 12 | 2 |
| 13 | 3 |
| 14 | 3 |
| 15 | 3 |
| 16 | 3 |
| 17 | 3 |
| 18 | 3 |
| 19 | 4 |
| 20 | 4 |
| 21 | 4 |
| 22 | 4 |
| 23 | 4 |
| 24 | 4 |
+-------+-------+
24 ROWS IN SET (0.00 sec)
寫一條sql語句，在t1中按照type分組，在每組中各取一個t1_id，找出它們對應的t2_id來，
個人比較習慣的還是使用in:
SELECT t2_id,t1_id FROM t2 WHERE t2.t1_id IN (SELECT t1_id FROM t1 GROUP BY TYPE);
在mysql5.5中看一下：
mysql> SELECT version();
+------------+
| version() |
+------------+
| 5.5.22-log |
+------------+
1 ROW IN SET (0.02 sec)
mysql> SELECT t2_id,t1_id FROM t2 WHERE t2.t1_id IN (SELECT t1_id FROM t1 GROUP BY TYPE);
+-------+-------+
| t2_id | t1_id |
+-------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
| 11 | 2 |
| 12 | 2 |
+-------+-------+
12 ROWS IN SET (0.01 sec)
mysql> EXPLAIN SELECT t2_id,t1_id FROM t2 WHERE t2.t1_id IN (SELECT t1_id FROM t1 GROUP BY TYPE);
+----+--------------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | REF | ROWS | Extra |
+----+--------------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| 1 | PRIMARY | t2 | INDEX | NULL | t1_id | 4 | NULL | 24 | USING WHERE; USING INDEX |
| 2 | DEPENDENT SUBQUERY | t1 | INDEX | NULL | TYPE | 4 | NULL | 1 | USING INDEX |
+----+--------------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
2 ROWS IN SET (0.00 sec)
返回12條數據，用到了DEPENDENT SUBQUERY， 如果數據量大的話， 效率會非常低。
可以把此語句改寫成inner join的形式。
據說mysql 5.6對於子查詢做了相當不錯的優化，
那就看看mysql5.6.9的表現：
mysql> SELECT version();
+--------------+
| version() |
+--------------+
| 5.6.9-rc-log |
+--------------+
1 ROW IN SET (0.00 sec)
mysql> SELECT t2_id,t1_id FROM t2 WHERE t2.t1_id IN (SELECT t1_id FROM t1 GROUP BY TYPE);
+-------+-------+
| t2_id | t1_id |
+-------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 2 |
| 8 | 2 |
| 9 | 2 |
| 10 | 2 |
| 11 | 2 |
| 12 | 2 |
| 13 | 3 |
| 14 | 3 |
| 15 | 3 |
| 16 | 3 |
