for example, if there is a HR database, there are two related tables: employee and performance . employee table is all the information of the employee. For example, there is department , and the structure of performance table is as follows:
performanceRating is primary key in performance table, and foreign key
employee table.
if you need to know the average PerformanceRaing, of each department and sort it in order from smallest to largest, then:
SELECT Avg(employee.PerformanceRating) AS [Average Performance Raing], employee.Department
FROM employee
GROUP BY employee.Department
ORDER BY Avg(employee.PerformanceRating);output result is
< table > < thead > < tr > < th > Average Performance Raing < / th > < th > Department < / th > < / tr > < / thead > < tbody > < tr > < td > 3.14655172413793 < / td > < td > Sales < / td > < / tr > < tr > < td > 3.15514018691589 < / td > < td > Research & Development < / td > < / tr > < tr > < td > 3.21212121212121 < / td > < td > Human Resources < / td > < / tr > < / tbody > < / table > but the department average performanceRating calculated in this way is not an integer, but if you also need to attach the description of performance next to the average, that is, the description in the performace table is also followed, not just showing the numbers, how should you operate it? you should not be able to directly INNER JOIN performance the table, right?
for example, the output result is
< table > < thead > < tr > < th > Department < / th > < th > Average Performance Raing < / th > < th > PerformanceDescriptor < / th > < / tr > < / thead > < tbody > < tr > < td > Sales < / td > < td > 3.14655172413793 < / td > < td > should be Excellent? < / td >
< / tr >
< tr >
< td > Research & Development < / td >
< td > 3.15514018691589 < / td >
< td > should be Excellent? < / td >
< / tr >
< tr >
< td > Human Resources < / td >
< td > 3.21212121212121 < / td >
< td > should be Excellent? < / td >
< / tr >
< / tbody >
< / table >