MySQL練習十二： 查找薪水記錄超過15次的員工號emp_no以及其對應的記錄次數(shù)t

題目：有一個薪水表，salaries簡況如下:

請你查找薪水記錄超過15次的員工號emp_no以及其對應的記錄次數(shù)t，以上例子輸出如下:

數(shù)據(jù)表：表結(jié)構(gòu)

drop table if exists  `salaries` ; 
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`));

數(shù)據(jù)：insert語句

INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');
INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');
INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');
INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');
INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');
INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');

解題思路：

1、用COUNT()函數(shù)和GROUP BY語句可以統(tǒng)計同一emp_no值的記錄條數(shù)。

2、根據(jù)題意，輸出的變動次數(shù)為t，故用AS語句將COUNT(emp_no)的值轉(zhuǎn)換為t。

3、由于COUNT()函數(shù)不可用于WHERE語句中，故使用HAVING語句來限定t>15的條件。

參考答案：

select emp_no, count(*) as t from salaries group by emp_no having t > 15;

推薦試題：

1. MySQL練習十二：查找所有已經(jīng)分配部門的員工的last_name和first_name以及dept_no
   

2. MySQL練習十一：查找當前薪水詳情以及部門編號dept_no
   

3. MySQL練習十：查找入職員工時間排名倒數(shù)第三的員工所有信息
   

4. MySQL練習九：查找最晚入職員工的所有信息