CREATE TABLE mytbl2(id INT primary key,NAME VARCHAR(16),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,‘zhang3’,33,101);
INSERT INTO mytbl2 VALUES(2,‘li4’,34,101);
INSERT INTO mytbl2 VALUES(3,‘wang5’,34,102);
INSERT INTO mytbl2 VALUES(4,‘zhao6’,34,102);
INSERT INTO mytbl2 VALUES(5,‘tian7’,36,102);
#查询每个部门年龄最大的人(错误的逻辑,name不对)
select name,max(age)
from mytbl2
group by dept
1.查询每个部分最大年龄
2查询人
正确的语句:
SELECT m.* FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age) maxage FROM mytbl2 GROUP BY dept) AS ab
ON ab.dept=m.dept AND m.age=ab.maxage
8个sql题
CREATE TABLE t_dept
(
id
INT(11) NOT NULL AUTO_INCREMENT,
deptName
VARCHAR(30) DEFAULT NULL,
address
VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE t_emp
(
id
INT(11) NOT NULL AUTO_INCREMENT,
name
VARCHAR(20) DEFAULT NULL,
age
INT(3) DEFAULT NULL,
deptId
INT(11) DEFAULT NULL,
empno INT NOT NULL,
PRIMARY KEY (id
),
KEY idx_dept_id
(deptId
)
#CONSTRAINT fk_dept_id
FOREIGN KEY (deptId
) REFERENCES t_dept
(id
)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(id,deptName,address) VALUES(1,‘华山’,‘华山’);
INSERT INTO t_dept(id,deptName,address) VALUES(2,‘丐帮’,‘洛阳’);
INSERT INTO t_dept(id,deptName,address) VALUES(3,‘峨眉’,‘峨眉山’);
INSERT INTO t_dept(id,deptName,address) VALUES(4,‘武当’,‘武当山’);
INSERT INTO t_dept(id,deptName,address) VALUES(5,‘明教’,‘光明顶