哪位云友帮我这句SQL调优啊

Question

 SELECT c.`name`,
(SELECT COUNT(r.rid) FROM `reward_punish` r LEFT JOIN student s ON s.stuno = r.student WHERE r.date > '2017-8-15 11:29:20' AND r.date < '2017-8-19 11:29:48' AND r.type = '10001' AND s.stuno=c.stuno) AS t1,
(SELECT COUNT(r.rid) FROM `reward_punish` r LEFT JOIN student s ON s.stuno = r.student WHERE r.date > '2017-8-15 11:29:20' AND r.date < '2017-8-19 11:29:48' AND r.type = '10002' AND s.stuno=c.stuno) AS t2
 FROM student c;

r.type(奖惩类型) 至多会存在30个，这样就会扩充为 t1...t30吗？

奖惩类型可能会存在30种，查询列表需要展示所有学生类型的次数

这种该如何破 SQL小白

谢谢

Answer 1

是每个学生，每种类型次数吧，下面sql，手写没运行，提供一种解决思路
select c.name, r.type, count(1) cs
from student c
left join (select *
from reward_punish r
where r.date > '2017-8-15 11:29:20'
AND r.date < '2017-8-19 11:29:48') r
on c.stuno = r.student
group by c.name, r.type