FROMサブクエリ

仮想のテーブルをもう一枚作ります.

元のデータ値がnullの場合、サブクエリによって作成されたテーブルには入らないので、カウントを書き込むときは

に注意してください.

WHERE節サブクエリ

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つ以上になります.

ハッカーランキングの問題に答える

Top Earners

WHEREセクションのサブクエリの使用

SELECT months*salary AS earnings,
        COUNT(*)
FROM employee
WHERE months*salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings

HAVINGセクションサブクエリの使用

SELECT months*salary AS earnings,
        COUNT(*)
FROM employee
GROUP BY earnings
HAVING months*salary = (SELECT MAX(months*salary) FROM employee)

Letcode解答

184.Department Highest Salary

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テーブルで、各部門の給与が最も高い人の給与と部門id->fromセクションのサブクエリ.

サブクエリを元のemployeeテーブルに結合し、サブクエリテーブルをemployeeテーブルの右側に貼り付けます->ただし、受かったときに給与の最大値を残すため、受かった条件はANDE.給与=hiです.max salを追加します.

最後にdepartmentテーブルに署名し、department名を表示します.
*/