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: 9675,
today: 1,
yesterday: 1
|