SQLプレミアムsubquery

9758 ワード

sql テキストリンク

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名を表示します.
*/

Reference

この問題について(SQLプレミアムsubquery), 我々は、より多くの情報をここで見つけました https://velog.io/@aza425/SQL-고급subquery

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

なぜ1行スクリプトタグでアクセシビリティを解決できないのか?