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

 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小白

谢谢

是每个学生，每种类型次数吧，下面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

如果需要每个学生转到一行可以对结果 SELECT user_name ,
MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME; 处理下，如果是oracle11g以上可以使用 pivot 函数

select c.name,a.rid,a.type from student c,
(SELECT COUNT(r.rid) "rid",r.type,s.stuno FROM reward_punish r LEFT JOIN student s ON s.stuno=c.stuno
WHERE r.date > '2017-8-15 11:29:20' AND r.date < '2017-8-19 11:29:48' group by r.type,stuno) a
where a.stuno=c.student;一个学生关联出type份数据，a.type可以不要
之后还可以用name或type分组

表结构什么样，要实现什么结果。。说清楚才好