server sql 众数_sql查询（三）--having子句求众数、中位数

一、建立需要查询的表

CREATE TABLE Graduates

(name VARCHAR(16) PRIMARY KEY,

income INTEGER NOT NULL);

-- 桑普森是个离群值，会拉高平均数

INSERT INTO Graduates VALUES(‘桑普森‘, 400000);

INSERT INTO Graduates VALUES(‘迈克‘, 30000);

INSERT INTO Graduates VALUES(‘怀特‘, 20000);

INSERT INTO Graduates VALUES(‘阿诺德‘, 20000);

INSERT INTO Graduates VALUES(‘史密斯‘, 20000);

INSERT INTO Graduates VALUES(‘劳伦斯‘, 15000);

INSERT INTO Graduates VALUES(‘哈德逊‘, 15000);

INSERT INTO Graduates VALUES(‘肯特‘, 10000);

INSERT INTO Graduates VALUES(‘贝克‘, 10000);

INSERT INTO Graduates VALUES(‘斯科特‘, 10000);

1、求众数

(1)在having子句中用包含谓词all 的子查询

SELECT income, COUNT(*) AS cnt

FROM Graduates

GROUP BY income HAVING COUNT(*) >= ALL ( SELECT COUNT(*) FROM Graduates GROUP BY income)；缺点：谓词all 用于null和 空集时会出现问题

(2)在having子句中 用