topic description
< table > < thead > < tr > < th > Employee < / th > < / tr > < / thead > < tbody > < tr > < td > Id < / td > < td > Name < / td > < td > Salary < / td > < td > DepartmentId < / td > < / tr > < tr > < td > 1 < / td > < td > Joe < / td > < td > 70000 < / td > < td > 1 < / td > < / tr > < tr > < td > 2 < / td > < td > Henry < / td > < td > 80000 < / td > < td > 2 < / td > < / tr > < tr > < td > 3 < / td > < td > Sam < / td > < td > 60000 < / td > < td > 2 < / td > < / tr > < tr > < td > 4 < / td > < td > Max < / td > < td > 90000 < / td > < td > 1 < / td > < / tr > < tr > < td > 5 < / td > < td > Janet < / td > < td > 69000 < / td > < td > 1 < / td > < / tr > < tr > < td > 6 < / td > < td > Randy < / td > < td > 85000 < / td > < td > 1 < / td > < / tr > < / tbody > < / table > TheEmployee table contains all employee information, with each employee having its own Id, salary and department Id.
< table > < thead > < tr > < th > Department < / th > < / tr > < / thead > < tbody > < tr > < td > Id < / td > < td > Name < / td > < / tr > < tr > < td > 1 < / td > < td > IT < / td > < / tr > < tr > < td > 2 < / td > < td > Sales < / td > < / tr > < / tbody > < / table > TheDepartment table contains information for all departments of the company.
write a SQL query to find out the top three highest-paid employees in each department.
related codes
/ / Please paste the code text below (do not replace the code with pictures)
answer:
SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Department d, Employee e
WHERE b.DepartmentId = d.Id
AND (
SELECT COUNT(DISTINCT Salary)
FROM Employee
WHERE DepartmentId = d.Id
AND Salary > e.Salary
) < 3
ORDER BY Department
what result do you expect? What is the error message actually seen?
how to understand the subqueries:
(SELECT COUNT (DISTINCT Salary)
FROM Employee
WHERE DepartmentId = d.Id
AND Salary > e.Salary) < 3
try it yourself
SELECT a.Salary
FROM Employee a ,Employee b WHERE a.Salary > b.Salaryfound that I still can"t understand the query logic.