如何使用关键词选择最小值 [英] how to select the min value using having key word

问题描述

我已经创建了表stu_dep_det

I have created the table stu_dep_det

CREATE TABLE  `stu_dept_cs` (
  `s_d_id` int(10) unsigned NOT NULL auto_increment,
  `stu_name` varchar(15) , `gender` varchar(15) , `address` varchar(15),`reg_no` int(10) ,
  `ex_no` varchar(10) ,
  `mark1` varchar(10) ,
  `mark2` varchar(15) ,
  `mark3` varchar(15) ,
  `total` varchar(15) ,
  `avg` double(2,0),
  PRIMARY KEY  (`s_d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=8 ;

然后插入值

INSERT INTO `stu_dept_cs` (`s_d_id`, `stu_name`, `gender`, `address`, `reg_no`, `ex_no`, `mark1`, `mark2`, `mark3`, `total`, `avg`) VALUES
(1, 'alex', 'm', 'chennai', 5001, 's1', '70', '90', '95', '255', 85),
(2, 'peter', 'm', 'chennai', 5002, 's1', '80', '70', '90', '240', 80),
(6, 'parv', 'f', 'mumbai', 5003, 's1', '88', '60', '80', '228', 76),
(7, 'basu', 'm', 'kolkatta', 5004, 's1', '85', '95', '56', '236', 79);

我想使用具有关键字来选择min(avg)，并且使用了以下sql语句

I want to select the min(avg) using having keyword and I have used the following sql statement

SELECT * FROM stu_dept_cs s  having  min(avg)

是否正确，请写正确的答案....

Is it correct or not plz write the correct ans....

推荐答案

select somecolumn1,somecolumn2
from stu_dept_cs 
group by somecolumn1,somecolumn2,avg
having avg = min(avg)

或

with t1
(select rownumber() over (partition by somecolumn1,somecolumn2 
    order by somecolumn1,somecolumn2,avg asc) as rownum
from stu_dept_cs )
select * from t1 where rownum=1

这篇关于如何使用关键词选择最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！