(group by 查询的视图 与需要的查询的语句进行关联查询)
1.mysql 使用
创建表:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`sex` tinyint(1) DEFAULT '0',
`score` int(10) NOT NULL,
`dept` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
添加测试数据:
insert into student (id,name,sex,score,dept) values(1,'a','1',90,'dev');
insert into student (id,name,sex,score,dept) values(2,'b','1',90,'dev');
insert into student (id,name,sex,score,dept) values(3,'b','0',88,'design');
insert into student (id,name,sex,score,dept) values(4,'c','0',60,'sales');
insert into student (id,name,sex,score,dept) values(5,'c','0',89,'sales');
insert into student (id,name,sex,score,dept) values(6,'d','1',100,'product');
mysql查询:每个部门只有一条数据,dev 部门的90 缺少了b这个人
select *,max(score) as max from student group by dept order by name;
oracle 查询:
select * from student t ,(select dept ,max(score) score from student group by dept )t2
where t.dept=t2.dept and t.score=t2.score