Let's take a look at how to simplify this mysql statement and optimize the query efficiency ~~~~~

Let's take a look at how to simplify this mysql statement and optimize the query efficiency ~~~~~ This post was last edited by setoy at 12:03:34

There are three tables: the administrator table, the department table, and the score table for the department as follows:


Now you need to query and count the scores of each department. even if a department does not have a score record, the score (0 points) is displayed. the query result is as follows:


The mysql statement has been completely confusing and I have written a piece of code:

Select ''as type, 'class '. 'name' as cname, 'Score '. 'CID', 'Score '. 'uid', 'user '. 'name' as uname, 'Score 'from 'score 'right join 'class' on 'score '. 'CID' = 'class '. 'CID' left join 'user' on 'score '. 'uid' = 'user '. 'uid' union all (select 'summary', 'C '. 'cname', 'C '. 'CID', 'C '. 'uid', count ('CID') as uname, sum ('score ') as score from (select ''as type, 'class '. 'name' as cname, 'Score '. 'CID', 'Score '. 'uid', 'user '. 'name' as uname, 'Score 'from 'score 'right join 'class' on 'score '. 'CID' = 'class '. 'CID' left join 'user' on 'score '. 'uid' = 'user '. 'uid') c group by 'cname') order by 'cname' asc, 'type' asc;

It seems that this SQL statement is too complicated! Can this statement be simplified? In addition, can the query efficiency be optimized?
Table structure, data, and code are all here: http://sqlfiddle.com /#! 2/6f5db6/10
Please take a look

Reply to discussion (solution)

Can I use a simple SQL statement and a php program for implementation?

Desc SQL
Add an index to the keyword field.

Desc SQL
Can I add an index to the keyword field to see if it is specific?