- 連結してGROUPで集計
- 結果をパースしやすいように,別名をつけておく.(検索結果のオブジェクトや配列にminとかmaxがあると面倒なので)
- min(books.asin) とか min(books.title) はたぶんよろしくない.
select books.id, min(books.asin) asin, min(books.title) title, count(*) user_num,
sum(userbooks.peta_cnt) peta_sum, max(userbooks.peta_date) peta_date
from userbooks
left outer join books on books.id = userbooks.bookid
left outer join users on userbooks.userid = users.id
group by userbooks.bookid;
select books.id, min(books.asin) asin, min(books.title) title, count(*) user_num,
sum(userbooks.peta_cnt) peta_sum, max(userbooks.peta_date) peta_date
from userbooks
left outer join books on books.id = userbooks.bookid
left outer join users on userbooks.userid = users.id
group by userbooks.bookid
order by peta_date DESC;