kantomi score 224
2015/05/18 14:20 投稿
| こういうことがしたいのでは？ |
| ```lang-＜SQL＞ |
| SELECT |
| main.seito_id |
| , st.name |
| , main.min_kyouka_id |
| , min_k.name |
| , main.min |
| , main.max_kyouka_id |
| , max_k.name |
| , main.max |
| , main.sum |
| FROM |
| (SELECT |
| m.seito_id |
| , (SELECT MIN(kyouka_id) as kyouka_id FROM t_score sb WHERE sb.seito_id = m.seito_id AND score = m.min) AS min_kyouka_id |
| , m.min |
| , (SELECT MIN(kyouka_id) as kyouka_id FROM t_score sb WHERE sb.seito_id = m.seito_id AND score = m.max) AS max_kyouka_id |
| , m.max |
| , m.sum |
| FROM |
| (SELECT |
| seito_id |
| , MIN(score) as min |
| , MAX(score) as max |
| , SUM(score) as sum |
| FROM t_score |
| GROUP BY |
| seito_id) m |
| seito_id |
| ORDER BY SUM(score) DESC |
| LIMIT 10 |
| ) m |
| ) main |
| INNER JOIN t_seito st -- m_seito の方が良いと思う |
| ON main.seito_id = st.t_seito.id |
| INNER JOIN m_kyouka min_k |
| ON main.min_kyouka_id = min_k.kyouka_id |
| INNER JOIN m_kyouka max_k |
| ON main.max_kyouka_id = max_k.kyouka_id |
| ``` |
