how to select the min value using having key word

问题:

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 ;

then Inserted the values

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);

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....

回答1:

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

or

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

回答2:

SELECT t1.* FROM stu_dept_cs t1
LEFT JOIN stu_dept_cs t2
ON t1.avg > t2.avg
WHERE t2.stu_name IS NULL;