SQL14 从titles表获取按照title进行分组 注意对于重复的emp_no进行忽略。

题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

CREATE TABLE IF NOT EXISTS`titles`(

`emp_no` int(11) NOT NULL,

`title` varchar(50) NOT NULL,

`from_date` date NOT NULL,

`to_date` date DEFAULT NULL);

如插入：

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');

INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');

INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');

INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');

INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');

INSERT INTO titles VALUES(10005,'Senior Staff','1996-09-12','9999-01-01');

INSERT INTO titles VALUES(10005,'Staff','1989-09-12','1996-09-12');

INSERT INTO titles VALUES(10006,'Senior Engineer','1990-08-05','9999-01-01');

INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

INSERT INTO titles VALUES(10007,'Staff','1989-02-10','1996-02-11');

INSERT INTO titles VALUES(10008,'Assistant Engineer','1998-03-11','2000-07-31');

INSERT INTO titles VALUES(10009,'Assistant Engineer','1985-02-18','1990-02-18');

INSERT INTO titles VALUES(10009,'Engineer','1990-02-18','1995-02-18');

INSERT INTO titles VALUES(10009,'Senior Engineer','1995-02-18','9999-01-01');

INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');

INSERT INTO titles VALUES(10010,'Engineer','1996-11-24','9999-01-01');

输入描述:

无

输出描述:

--方法1：GROUPBY+HAVING，子查询，先去重再计数SELECT title, COUNT(*) tFROM (SELECT DISTINCT emp_no,title FROM titles)GROUP BY titleHAVING t>=2;--补充：当DISTINCT应用到多个字段时，其应用范围是其后面的所有字段，而不是紧挨它的一个字段--注意：DISTINCT只能放在所有字段前面，所以上面的DISTINCT emp_no和title不可以交换--方法2：Distinct，考虑去重的部分中计数SELECT title, COUNT(DISTINCT emp_no) tFROM titlesGROUP BY titleHAVING t>=2;

补充：当DISTINCT应用到多个字段时，其应用范围是其后面的所有字段，而不是紧挨它的一个字段

注意：DISTINCT只能放在所有字段前面，所以上面的DISTINCT emp_no和title不可以交换

如果觉得《SQL14 从titles表获取按照title进行分组 注意对于重复的emp_no进行忽略。》对你有帮助，请点赞、收藏，并留下你的观点哦！