SOY Shopで実際に実稼働しているデータベースから齋藤という名前のつく人の注文回数を調べて一番注文した人をあげてみる。
はじめにSOY Shopのデータベースのスキーマを確認する。スキーマは下記のURLから確認することが出来る。
https://github.com/inunosinsi/soycms/blob/master/cms/soyshop/webapp/src/logic/init/sqlite.sql
今回取得したいのは、注文のテーブル(soyshop_order)の注文ID(id)、顧客ID(user_id)と金額(price)、
顧客のテーブル(soyshop_user)からは顧客名(name)になる。
sqlite> SELECT o.id,user_id,price,name FROM soyshop_order o INNER JOIN soyshop_user u ON o.user_id=u.id WHERE name LIKE '齋藤%';
これで、注文idと顧客idとpriceと名前が出る。
まず、SELECT FROM INNERN JOIN ONを使用して、soyshop_orderとsoyshop_userのtableをつなげる。
このとき、tableの中の一致するフィールドを探す。この場合は、soyshop_orderのuser_idとsoyshop_userのidが一致するので、一致する箇所をONの後に記載し、＝でつなげる。また、FROMの後で、soyshop_orderを oとし、 soyshop_userをuと設定することでこの一文の中でてくるsoyshop_orderと soyshop_userをoとuで表わすことができる。
60|2|500|齋藤 毅 91|1|500|齋藤 毅 112|50|500|齋藤毅 137|64|1000|齋藤毅 138|64|1000|齋藤毅 139|64|1000|齋藤毅 140|70|500|齋藤 亮子 141|64|500|齋藤毅 142|70|500|齋藤 亮子 143|64|10|齋藤毅 144|64|10|齋藤毅 145|70|500|齋藤 亮子 146|70|10|齋藤 亮子
上記の金額と名前を出したい場合は、同じユーザーidを集めてグループにしてSUM関数で合算できる。
sqlite> SELECT SUM(price),name FROM soyshop_order o INNER JOIN soyshop_user u ON o.user_id=u.id WHERE name LIKE '齋藤%' GROUP BY u.id;
GROUP BY u.idを最後につけることで、soyshop_userの中のidをグループにする。さらにWHERE区をつけることで、齋藤という名前の人だけの金額を集計する。
5010|齋藤 毅 22120|齋藤 毅 500|齋藤毅 15320|齋藤毅 6380|齋藤 亮子 1500|齋藤毅 1000|齋藤毅テスト 1000|齋藤毅テスト 500|齋藤毅テスト 500|齋藤毅テスト
注文者の注文回数を出したい場合は、COUNT関数を使用する。
sqlite> SELECT Count(o.id),name FROM soyshop_order o INNER JOIN soyshop_user u ON o.user_id=u.id WHERE name LIKE '齋藤%' GROUP BY u.id;
これで、回数と注文者が下記のように表される。
4|齋藤 毅 22|齋藤 毅 1|齋藤毅 24|齋藤毅 19|齋藤 亮子 3|齋藤毅 2|齋藤毅テスト 2|齋藤毅テスト 1|齋藤毅テスト 1|齋藤毅テスト
