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



Counter: 7788, today: 1, yesterday: 0

トップ   編集 凍結解除 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2013-09-24 (火) 14:56:18 (1369d)