mysql-サブクエリ

意味
サブクエリまたは内部クエリと呼ばれる他の文の内部にネストされたselect文.内部にネストされた他のselect文のクエリーを、外部クエリーまたはプライマリクエリーと呼びます.
ぶんかつ
クエリーから表示される場所別に分類:

-selectの後:標量子クエリ

のみをサポート

from後:サポートテーブルサブクエリ

whereまたはhavingの後:標準量子クエリーとカラムサブクエリーをサポート

existsの後(関連サブクエリ):サポートテーブルサブクエリ

結果セットの列数によって異なります.

標準量子クエリ(結果セットは1行1列のみ)は、単行サブクエリ

とも呼ばれる.

列サブクエリ(結果セットは複数行1列)は、複数行サブクエリ

とも呼ばれる.

行サブクエリ(結果セットは複数行複数列)

テーブルサブクエリ(結果セットは一般的に複数行の複数列)は、ネストされたサブクエリ

とも呼ばれます.
ひょうりょうしクエリー
一方通行サブクエリとも呼ばれ、whereまたはhavingの後に現れる特徴1サブクエリは括弧内に置く②サブクエリは一般的に条件の右側に置く③標準量子クエリは、一般的に一方通行オペレータと組み合わせて使用され、例えば><>④列サブクエリは、一般的に複数行オペレータと組み合わせて使用される.例えば、in、anyall⑤サブクエリの実行はメインクエリより優先される.プライマリ・クエリの条件は、サブクエリの結果を使用します.
ケース1:誰の給料がAbelより高いか①まずAbelの給料を調べる

SELECT salary 
FROM employees
WHERE last_name='Abel';

②従業員の情報を照会し、salary>①の結果を求める

select * from employees
where salary>(
	SELECT salary 
	FROM employees
	WHERE last_name='Abel'
);

ケース2:jobを返すidは143番と同じで、salaryは143番より多い名前、job_id、給料と①まず調べて、143号社員のjob_id

SELECT job_id
FROM employees
WHERE employee_id=143;

②続いて143号社員の給与を照会する

SELECT salary
FROM employees
WHERE employee_id=143;

③最後に従業員のlast_を問い合わせるname,job_id,salary要求:job_id=①結果、salary>②結果

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=143
)
AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);

ケース3:会社の給料が最も少ない従業員を返すlast_name,job_id,salary①まず会社の最低賃金を調べる

select min(salary)
from employees;

②次にlast_を検索name,job_id,salary,要求salary=①結果

select last_name,job_id,salary
from employees
where salary=(
	SELECT MIN(salary)
	FROM employees
);

ケース4:最低賃金が50番より大きい部門の最低賃金の部門idとその最低賃金を照会する
①50番部門の最低賃金照会

SELECT MIN(salary)
FROM employees
WHERE department_id=50;

②部門別最低賃金照会

select min(salary)
from employees
group by department_id;

③②に基づいて選別し、min(salary)>①を満たす

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
having min(salary)>(
	select min(salary)
	from employees
	where department_id=50
);

列サブクエリ
複数行サブクエリとも呼ばれ、複数行比較オペレータと組み合わせて使用する必要があります
オペレータ
意味
IN/NOT IN
リストのいずれかに等しい
ANY|SOME
サブクエリが返す値との比較
ALL
サブクエリで返されるすべての値との比較
ケース1:location_を返すidは1400または1700の部門のすべての従業員の名前です①部門番号1400または1700のlocation_を先に照会しますidはどれらが1つのテーブル(1列の複数行)を構成します

select distinct department_id
from departments
where location_id in (1400,1700);

②従業員名照会、要求部署番号①リストのいずれか

SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

ケース2:jobより他の部門に戻るidは‘IT_PROG'部門のいずれかの給料の低い従業員:勤務番号、名前、job_id,salary①まずjob_を検索するid=‘IT_PROG’の全従業員の給料

SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG';

②従業員の勤務番号、氏名、jobを照会するid,salary,要求salary

select employee_id,last_name,job_id,salary
from employees
where salary < any(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) and job_id <> 'IT_PROG';

または、いずれかの値より小さい値を最大値より小さい値に変更できます.

select employee_id,last_name,job_id,salary
from employees
where salary 'IT_PROG';

ケース3:他の職種の比jobを返すidは'IT_PROG’のすべての給料が低い従業員の勤務番号、名前、job_id,salary.

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id='IT_PROG'
) 
AND job_id <> 'IT_PROG';

あるいは

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary 'IT_PROG';

行サブクエリ
結果セットが1行の複数列または複数行の複数列の場合:従業員番号が最小で最も給与が高い従業員情報を問い合わせる①最小の従業員番号を問い合わせる

SELECT MIN(employee_id)
FROM employees; 

②最高賃金照会

SELECT MAX(salary)
FROM employees;

③従業員情報の照会

SELECT * FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)
AND salary=(
	SELECT MAX(salary)
	FROM employees
);

行サブクエリを使用して書き込み:

SELECT * 
FROM employees
WHERE(employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

サブクエリはselectの後ろに配置されます
スカラー量子クエリのみをサポート

SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
)    
FROM departments d;

照会従業員番号=102の部門名

SELECT(
  SELECT d.department_name
  FROM departments d
  INNER JOIN employees e
  ON d.department_id=e.department_id
  WHERE e.employee_id=102
);

fromの後ろに置く
サポート表サブクエリサブクエリサブクエリ結果を1つの表として使用するには、テーブルに別名ケースを付ける必要があります:各部門の平均給与を問い合わせる①各部門の平均給与を先に問い合わせる

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

②接続①の結果セットとjob_grades表、平均賃金between lowest_をフィルタsal and higest_sal

SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`

existsの後ろに置く
関連サブクエリは、スケール量子クエリ、列サブクエリ、行サブクエリ、テーブルサブクエリselect exists(select employee_id from employees)をサポートするとも呼ばれる.結果は1または0のケース:従業員がいる部門名を問い合わせる

SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
     FROM employees e
     WHERE d.`department_id`=e.`department_id`
);

INキーの使用

SELECT department_name
FROM departments d
WHERE d.`department_id` IN (
   SELECT department_id
   FROM employees
)

練習問題:1、ZlotKeyと同じ部署の社員の名前と給料を調べる①まず社員ZlotKeyがいる部署を調べる

SELECT department_id
FROM employees
WHERE last_name='ZlotKey';

②従業員の氏名・給与を照会し、当該従業員の部署を求める＝①の結果

SELECT last_name,salary
FROM employees
WHERE department_id =(
	SELECT department_id
	FROM employees
	WHERE last_name='ZlotKey'
);

2、給料が会社の平均より高い従業員の従業員番号、氏名と給料を調べる①平均給料を調べる

SELECT AVG(salary)
FROM employees;

②給与が①より大きい従業員番号、氏名、給与の照会

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

3、各部門における給与が本部門の平均給与より高い従業員の従業員番号、氏名、給与①各部門の平均給与を調べる

select avg(salary),department_id
from employees
group by department_id;

②接続①の結果セットとemployeesテーブル

SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary >ag_dep.ag;

4、照会と氏名にアルファベットuを含む従業員同一部門の従業員の従業員番号と氏名①照会氏名にアルファベットuを含む従業員の所在部門

SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE('%u%');

②従業員の従業員番号、氏名を照会し、当該従業員が所在する部門番号が①に等しいことを要求した結果

select employee_id,last_name
from employees 
where department_id in(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE('%u%')
);

5、照会部門のlocation_id 1700の部門で働く従業員の従業員番号①location_を照会するid=1700の部門はどれらがあります

select distinct department_id
from departments
where location_id=1700;

②照会部署番号=①のいずれかの従業員番号

select employee_id
from employees
where department_id = ANy(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id=1700
);

6、照会管理者がKingの社員の名前と給料①照会名がKingの社員番号

select employee_id
from employees
where last_name ='K_ing';

②どの社員のmanagerを問い合わせるかid=1のemployee_id

select last_name,salary
from employees
where manager_id in(
	SELECT employee_id
	FROM employees
	WHERE last_name ='K_ing'
);

7、給料が一番高い従業員の名前を調べ、firstを要求する.nameとlast_nameは列として表示され、姓、名①は最高賃金を照会する

SELECT MAX(salary)
FROM employees;

②給与照会=①の従業員の姓、名

SELECT CONCAT(first_name,last_name) " ， "
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

サブクエリの古典的なケース1、最低賃金の従業員情報をクエリする:last_name,salry#①まず最低賃金を調べる

SELECT MIN(salary)
FROM employees;

#②クエリーlast_name,salary要求salary=①の結果

SELECT last_name,salary 
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

二、平均給与が一番低い部門情報を調べる①各部門の平均給与

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;

#②照会①結果の最低平均賃金SELECT MIN(ag)、FROM(SELECT MIN(salary)ag、department_id FROM employees GROUP BY department_id )ag_dep; #③最低平均賃金を求める部門番号

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
ORDER  BY MIN(salary)
LIMIT 1;

#④部門情報の照会

SELECT * FROM 
departments
WHERE department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MIN(salary)
	LIMIT 1
);

三、平均賃金が最も低い部門情報と当該部門の平均賃金を照会する

SELECT d.*,ag
FROM departments d
INNER JOIN (
	SELECT department_id,AVG(salary)ag
	FROM employees
	GROUP BY department_id
	ORDER BY MIN(salary)
	LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;

四、平均賃金が最も高いjob情報を照会する1 jobごとの平均賃金を照会する

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#②クエリーjob_id最高の平均賃金

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1;

#③job情報の照会

SELECT *
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

五、平均给料が会社の平均给料より高い部门を调べてどれらがあります

SELECT AVG(salary) FROM employees;

#②部門別平均給与照会

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

#③②結果セットを選別し、平均賃金を要求>①

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary) FROM employees
);

六、会社のすべてのmanagerの詳細を問い合わせる#1すべてのmanagerの従業員番号を問い合わせる

SELECT DISTINCT manager_id
FROM employees;

#②詳細の照会、managerの要求id=①のいずれか

SELECT *
FROM employees
WHERE employee_id= ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

七、各部門の中で一番高い給料の中で一番低い部門の最低給料はいくらですか①各部門の一番高い給料の中で一番低い部門番号を調べます

SELECT DISTINCT department_id
FROM employees;
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1;

#②照会①結果の部門の最低賃金

SELECT MIN(salary),department_id
FROM employees;
WHERE department_id=(
	SELECT DISTINCT department_id
	FROM employees;
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

八、平均賃金が最も高い部門のmanagerの詳細を調べる:last_name,department_id,salary,email#1平均給与が最も高い部門番号を照会

SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)DESC
LIMIT 1;

#②employeesをdepartmentsテーブルに接続し、フィルタ条件=①

SELECT last_name,e.department_id,salary,email
FROM employees e
INNER JOIN departments d
ON e.`employee_id`=d.`manager_id`
WHERE d.department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)DESC
	LIMIT 1
);