SELECT *
FROM crimes
WHERE date = (SELECT MIN(date) FROM crimes)
= 으로 들어갈 때, 서브쿼리의 결과물이 1개 여야 한다.
SELECT *
FROM crimes
WHERE date IN (SELECT date FROM crimes ORDER BY date DESC LIMIT 5)
IN 또는 OR 으로 들어갈 때, 서브쿼리의 결과물이 1개이상이 가능하다.
SELECT months*salary AS earnings,
COUNT(*)
FROM employee
WHERE months*salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings
SELECT months*salary AS earnings,
COUNT(*)
FROM employee
GROUP BY earnings
HAVING months*salary = (SELECT MAX(months*salary) FROM employee)
SELECT d.name AS department
,e.name AS employee
,e.salary
FROM employee AS e
INNER JOIN (
SELECT departmentid, MAX(salary) AS max_salary
FROM employee
GROUP BY departmentid
) AS dh ON e.departmentid = dh.departmentid
AND e.salary=dh.max_salary
INNER JOIN department AS d ON d.id=e.departmentid
과정
SELECT * FROM employee AS e INNER JOIN ( SELECT departmentid, MAX(salary) AS max_sal FROM employee GROUP BY departmentid) AS hi ON e.departmentid = hi.departmentid INNER JOIN department AS d on d.id=e.departmentid
- 결과:
["Id", "Name", "Salary", "DepartmentId", "departmentid", "max_sal", "Id", "Name"],
[[1, "Joe", 70000, 1, 1, 90000, 1, "IT"],
[2, "Jim", 90000, 1, 1, 90000, 1, "IT"],
[3, "Henry", 80000, 2, 2, 80000, 2, "Sales"],
[4, "Sam", 60000, 2, 2, 80000, 2, "Sales"],
[5, "Max", 90000, 1, 1, 90000, 1, "IT"]]}
SELECT *
FROM employee AS e
INNER JOIN (
SELECT departmentid, MAX(salary) AS max_sal
FROM employee
GROUP BY departmentid) AS hi ON e.departmentid = hi.departmentid AND e.salary = hi.max_sal
INNER JOIN department AS d on d.id=e.departmentid
- 결과
["Id", "Name", "Salary", "DepartmentId", "departmentid", "max_sal", "Id", "Name"],
[[2, "Jim", 90000, 1, 1, 90000, 1, "IT"],
[3, "Henry", 80000, 2, 2, 80000, 2, "Sales"],
[5, "Max", 90000, 1, 1, 90000, 1, "IT"]]}
/*
1. Employee테이블에서 각 부서별로 가장 많은 salary를 받는 사람의 salary와 departmentid -> from 절의 서브쿼리로
서브쿼리와 원래의 employee테이블을 이너조인해서 employee 테이블오른쪽에 서브쿼리 테이블이 붙게 한다. -> 단, 붙을때 salary최대값만 남게 하기 위해서 붙이는 조건에 AND e.salary = hi.max_sal를 추가한다.
마지막으로 department 테이블과 조인을 하여 department 이름이 보일 수 있도록 한다.
*/