MySQL/GroupBy

GROUP BY †

mysql> SELECT * FROM sample WHERE sample_m_id = 28 ;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        44 |          28 | 28_0              |             1 |
|        45 |          28 | 28_0              |             2 |
|        46 |          28 | 28_4              |             1 |
|        47 |          28 | 28_4              |             2 |
|        48 |          28 | 28_5              |          NULL |
+-----------+-------------+-------------------+---------------+

mysql> SELECT * FROM sample WHERE sample_m_id = 28 GROUP BY type_id;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        48 |          28 | 28_5              |          NULL |
|        44 |          28 | 28_0              |             1 |
|        45 |          28 | 28_0              |             2 |
+-----------+-------------+-------------------+---------------+

GROUP BYした時、どのカラムの値が取得されるのか？ †

id(PRIMARY KEY)の上位をとってきてるのかな。

mysql> SELECT * FROM sample WHERE sample_m_id = 28 GROUP BY sample_m_id;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        44 |          28 | 28_0              |             1 |
+-----------+-------------+-------------------+---------------+

mysql> SELECT * FROM (SELECT * FROM sample ORDER BY sample_id DESC ) as tmp WHERE sample_m_id = 28 GROUP BY sample_m_id;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        48 |          28 | 28_5              |          NULL |
+-----------+-------------+-------------------+---------------+

GROUP BY句にDESC/ASCが指定できる †

mysql> SELECT * FROM sample WHERE sample_m_id = 28 GROUP BY sample_number DESC;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        48 |          28 | 28_5              |          NULL |
|        46 |          28 | 28_4              |             2 |
|        44 |          28 | 28_0              |             1 |
+-----------+-------------+-------------------+---------------+

mysql> SELECT * FROM sample WHERE sample_m_id = 28 GROUP BY sample_number ASC;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        44 |          28 | 28_0              |             1 |
|        46 |          28 | 28_4              |             2 |
|        48 |          28 | 28_5              |          NULL |
+-----------+-------------+-------------------+---------------+

HAVING句 †

HAVING句はWHERE句が評価された後に評価される。

mysql> SELECT * WHERE sample_m_id = 28 GROUP BY type_id HAVING sample_id >= 45;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        48 |          28 | 28_5              |          NULL |
|        45 |          28 | 28_0              |             2 |
+-----------+-------------+-------------------+---------------+

mysql> SELECT * FROM order_table WHERE sample_m_id = 28 AND sample_id >=45  GROUP BY type_id;
+-----------+-------------+-------------------+---------------+
| sample_id | sample_m_id | sample_number     | type_id       |
+-----------+-------------+-------------------+---------------+
|        48 |          28 | 28_5              |          NULL |
|        46 |          28 | 28_4              |             1 |
|        45 |          28 | 28_0              |             2 |
+-----------+-------------+-------------------+---------------+

Comment †