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 ~~~~~ At last, this post was edited by setoy from 2013-12-1912: 03: 34 and has three tables: the administrator table, department table, and the administrator's score table for the department, as shown below: now we need to query the scores of each department and make statistics to help us see how to simplify this mysql statement and optimize the query efficiency ~~~~~
At the end of this post, setoy edited three tables at 12:03:34 on: The administrator table, department table, and the score table of the administrator for the department, as shown below:


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 and share it with us: More