MySQL简单题目分组+组内计数

墨蓝 2023-01-23 08:00 26阅读 0赞

##### 注：相关题目均来自牛客题霸 #####

--------------------

## 题目：查找薪水记录超过15次的员工号emp\_no以及其对应的记录次数t ##

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`));
    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');

## 解法： ##

-- group by 之后（分组）,count是对每组中的数据进行count（组内计数）
    select emp_no , count(emp_no) t
    from salaries group by emp_no
    having t > 15