oracle 与 mysql group by 使用

2019独角兽企业重金招聘Python工程师标准>>>

（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

 

 

转载于:https://my.oschina.net/v512345/blog/794973