user表
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT ‘’,
level int(11) DEFAULT ‘0’,
team int(11) DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE = InnoDB AUTO_INCREMENT = 10 DEFAULT CHARSET = utf8;
INSERT INTO `user` (`id`, `name`, `level`, `team`)
VALUES
(1, '李宇春', 1, 1),
(2, '杨幂', 2, 1),
(3, '刘亦菲', 2, 1),
(4, '王子文', 4, 2),
(5, '张博之', 4, 2),
(6, '刘德华', 2, 2),
(7, '郭富城', 2, 3),
(8, '周杰伦', 2, 3),
(9, '蕾哈娜', 3, 3);
要求1
请你用一条语句查询出user表中每个组中级别最大的用户信息,并且最大级别大于3的
要求结果如下:
答案
select
max(level) level,
team
from
user
group by
team
having
max(level)> 3
要求2
请你用一条sql语句查询出user表中每个组中级别最大的用户的信息,要求如果每个组中如果级别最大的用户有多大,也请一并输出。要求的结果如下:
答案
select
a.*
from
user a
inner join (
select
max(level) as level,
team
from
user
group by
team
) b on a.level = b.level
and a.team = b.team