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 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: mysql statements have always been used to help you see how to simplify this mysql statement and optimize 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
Share: More
------ Solution --------------------
Can I use a simple SQL statement and a php program for implementation?
------ Solution --------------------
Desc SQL
Add an index to the keyword field.