0

MySQL GROUP 問題

+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| fk | varchar(20) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+

SELECT * FROM gg;
+----+------+------+
| id | name | fk |
+----+------+------+
| 1 | a | aaa |
| 2 | b | aaa |
| 3 | c | bbb |
+----+------+------+

SELECT * FROM gg GROUP BY fk HAVING MAX(id);

+----+------+------+
| id | name | fk |
+----+------+------+
| 1 | a | aaa |
| 3 | c | bbb |
+----+------+------+

3 個回答

4
fillano
iT邦超人 1 級 ‧ 2011-05-11 17:59:00

``````&lt;pre class="c" name="code">
SELECT * FROM (SELECT * FROM gg ORDER BY id DESC) AS A GROUP BY fk;
``````

``````&lt;pre class="c" name="code">
SELECT * FROM gg WHERE id IN (SELECT MAX(id) FROM gg GROUP BY fk);
``````

fillano iT邦超人 1 級 ‧ 2011-05-11 18:07:51 檢舉

``````&lt;pre class="c" name="code">
SELECT a.id, b.name, b.fk FROM (SELECT MAX(id) FROM gg GROUP BY fk) AS a JOIN gg AS b on a.id=b.id;
``````
chan15 iT邦新手 5 級 ‧ 2011-05-11 18:10:54 檢舉

fillano 大這樣是可以

fillano iT邦超人 1 級 ‧ 2011-05-11 18:13:56 檢舉

SELECT a.id, b.name, b.fk FROM (SELECT MAX(id) FROM gg GROUP BY fk) AS a JOIN gg AS b on a.id=b.id;

``````&lt;pre class="c" name="code">
SELECT a.id, b.name, b.fk FROM (SELECT MAX(id) as id FROM gg GROUP BY fk) AS a JOIN gg AS b on a.id=b.id;
``````

fillano iT邦超人 1 級 ‧ 2011-05-11 18:18:31 檢舉

``````&lt;pre class="c" name="code">
SELECT a.id, a.count, b.name, b.fk
FROM (SELECT MAX(id) as id, count(id) as count FROM gg GROUP BY fk) AS a
JOIN gg AS b on a.id=b.id;
``````
a802216 iT邦新手 4 級 ‧ 2011-05-12 13:45:52 檢舉

fillano iT邦超人 1 級 ‧ 2011-05-12 17:33:50 檢舉

``````&lt;pre class="c" name="code">
mysql> explain SELECT * FROM (SELECT * FROM gg ORDER BY id DESC) AS A GROUP BY fk;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | &lt;derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
|  2 | DERIVED     | gg         | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.00 sec)
``````
fillano iT邦超人 1 級 ‧ 2011-05-12 17:35:03 檢舉
``````&lt;pre class="c" name="code">
mysql> explain SELECT * FROM gg WHERE id IN (SELECT MAX(id) FROM gg GROUP BY fk);
+----+--------------------+-------+-------+---------------+--------+---------+------+------+----------------+
| id | select_type        | table | type  | possible_keys | key    | key_len | ref  | rows | Extra          |
+----+--------------------+-------+-------+---------------+--------+---------+------+------+----------------+
|  1 | PRIMARY            | gg    | ALL   | NULL          | NULL   | NULL    | NULL |    3 | Using where    |
|  2 | DEPENDENT SUBQUERY | gg    | index | NULL          | idx_fk | 22      | NULL |    3 | Using filesort |
+----+--------------------+-------+-------+---------------+--------+---------+------+------+----------------+
2 rows in set (0.00 sec)
``````
fillano iT邦超人 1 級 ‧ 2011-05-12 17:36:52 檢舉
``````&lt;pre class="c" name="code">
mysql> explain SELECT a.id, b.name, b.fk FROM (SELECT MAX(id) as id FROM gg GROUP BY fk) AS a JOIN gg AS b on a.id=b.id;
+----+-------------+------------+--------+---------------+---------+---------+------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra                           |
+----+-------------+------------+--------+---------------+---------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | &lt;derived2> | ALL    | NULL          | NULL    | NULL    | NULL |    2 |                                 |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |    1 |                                 |
|  2 | DERIVED     | gg         | ALL    | NULL          | NULL    | NULL    | NULL |    3 | Using temporary; Using filesort |
``````
fillano iT邦超人 1 級 ‧ 2011-05-12 21:29:23 檢舉

``````&lt;pre class="c" name="code">
ALTER TABLE `gg` ADD INDEX `idx_fkid` (fk, id);
``````

fillano iT邦超人 1 級 ‧ 2011-05-12 23:45:12 檢舉

``````&lt;pre class="c" name="code">
starting	0.000116
Opening tables	0.000017
System lock	0.000007
Table lock	0.000061
optimizing	0.000008
statistics	0.000029
preparing	0.000015
executing	0.000011
Sorting result	0.000004
Sending data	0.000120
init	0.000019
optimizing	0.000015
statistics	0.000029
preparing	0.000013
executing	0.000003
Sending data	0.000113
end	0.000006
end	0.000003
query end	0.000004
freeing items	0.000010
closing tables	0.000004
removing tmp table	0.000011
closing tables	0.000006
logging slow query	0.000004
cleaning up	0.000004
``````

fillano iT邦超人 1 級 ‧ 2011-05-13 00:07:56 檢舉

2
liurambo0911
iT邦高手 1 級 ‧ 2011-05-11 16:35:09

chan15提到：
SELECT * FROM gg GROUP BY fk HAVING MAX(id)

SELECT MAX(id),* FROM gg GROUP BY fk

chan15 iT邦新手 5 級 ‧ 2011-05-11 16:40:07 檢舉

2
seasuwang
iT邦新手 5 級 ‧ 2011-05-14 22:55:57

http://www.artfulsoftware.com/infotree/queries.php#78

fillano iT邦超人 1 級 ‧ 2011-05-16 10:09:02 檢舉