資料庫SQL實戰（牛客網）：統計各個部門的工資記錄數

統計各個部門的工資記錄數，給出部門編碼dept_no、部門名稱dept_name以及次數sum

CREATE TABLE

departments

(

dept_no

char(4) NOT NULL,

dept_name

varchar(40) NOT NULL,

PRIMARY KEY (

dept_no

));

CREATE TABLE

dept_emp

(

emp_no

int(11) NOT NULL,

dept_no

char(4) NOT NULL,

from_date

date NOT NULL,

to_date

date NOT NULL,

PRIMARY KEY (

emp_no

,

dept_no

));

CREATE TABLE

salaries

(

emp_no

int(11) NOT NULL,

salary

int(11) NOT NULL,

from_date

date NOT NULL,

to_date

date NOT NULL,

PRIMARY KEY (

emp_no

,

from_date

));

先将dept_emp與departments連接配接成一張新的臨時表，然後再與第三張表salaries連接配接，最後按dept_no後利用count()函數求薪水的記錄行數

select a.dept_no , a.dept_name , count(c.salary) as sum
from (dept_emp as b join departments as a on a.dept_no = b.dept_no)
join salaries as c on b.emp_no = c.emp_no
group by a.dept_no;