題目描述

從titles表獲取按照title進(jìn)行分組，每組個(gè)數(shù)大于等于2，給出title以及對(duì)應(yīng)的數(shù)目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);

插入數(shù)據(jù)

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

解答

select title,count(emp_no) num from titles group by title having num >= 2

結(jié)果展示

---

題目描述

從titles表獲取按照title進(jìn)行分組，每組個(gè)數(shù)大于等于2，給出title以及對(duì)應(yīng)的數(shù)目t。
注意對(duì)于重復(fù)的emp_no進(jìn)行忽略。

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

解答

SELECT title,count(DISTINCT emp_no) AS t FROM titles GROUP BY title HAVING t >= 2;

結(jié)果展示

超強(qiáng)干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達(dá)旦的技術(shù)人生

總結(jié)

以上是生活随笔為你收集整理的每日两SQL（7），欢迎交流~的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。