*GROUP BY [#o53fc7ed]

 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した時、どのカラムの値が取得されるのか? [#yede21de]
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が指定できる [#qf036e28]

 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句 [#l8a2f115]
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 |
 +-----------+-------------+-------------------+---------------+

&br;

*Comment [#v685ab56]

#comment_nospam

&br;

#counter

IP:220.220.219.251 TIME:"2008-05-23 (金) 02:26:36" REFERER:"http://wiki.minaco.net/index.php?cmd=edit&page=MySQL%2FGroupBy" USER_AGENT:"Mozilla/5.0 (Windows; U; Windows NT 5.1; ja; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14"

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS