mysql双重分组_MySQL-分组查询（GROUP BY）及二次筛选（HAVING）

为了测试GROUP BY 语句，我们创建两张表，并往表中添加数据

-- 创建部门表

CREATE TABLE IF NOT EXISTS department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depName VARCHAR(20) NOT NULL UNIQUE

);

-- 添加部门

INSERT department(depName) VALUES('开发部');

INSERT department(depName) VALUES('视频部');

INSERT department(depName) VALUES('教学部');

INSERT department(depName) VALUES('运营部');

-- 创建员工表

CREATE TABLE IF  NOT EXISTS employee(

id Int UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

addr VARCHAR(50) NOT NULL DEFAULT '北京',

salary FLOAT(6,2) NOT NULL DEFAULT 0,

sex ENUM('男','女','保密'),

depId TINYINT UNSIGNED

);

-- 添加员工记录

INSERT employee(username,age,addr,salary,sex,depId) VALUES('张三','21','山东','5432.12','男',1);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('李四','32','河北','6432.00','男',2);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('王五','26','北京','5932.92','女',3);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('赵六','32','上海','6232.14','男',4);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('Mr Adword','55','美国','9432.99','男',4);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('田七','19','北京','4932.92','保密',1);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('孙八','62','上海','9932.14'