一、表结构

CREATE TABLE users (

id int(11) NOT NULL AUTO_INCREMENT,

user_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,

age int(11) NULL DEFAULT NULL,

dept_id int(11) NULL DEFAULT NULL,

gz int(11) NULL DEFAULT NULL,

PRIMARY KEY (id) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO users VALUES (1, '张三', 90, 1, 2000);

INSERT INTO users VALUES (2, '李四', 30, 1, 3000);

INSERT INTO users VALUES (3, '王五', 25, 1, 2500);

INSERT INTO users VALUES (4, '张三丰', 120, 2, 50000);

INSERT INTO users VALUES (5, '张无忌', 90, 2, 20000);

INSERT INTO users VALUES (6, '张翠山', 50, 2, 10000);

INSERT INTO users VALUES (7, '赵敏', 80, 3, 888);

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (

dept_id int(11) NOT NULL AUTO_INCREMENT,

dept_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,

PRIMARY KEY (dept_id) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO dept VALUES (1, '财务部');

INSERT INTO dept VALUES (2, '行政部');

INSERT INTO dept VALUES (3, '开发部');

二、执行sql

1、全部数据

2、每个部门中年龄最大的2人的执行结果

select * from users u where (select count(0) from users d where d.dept_id=u.dept_id and u.age