<strong><span style="font-size:18px;">//数据库test中的表student。字段name,class,score,分别代表姓名,班级,分数。
//1.算出每个班级中的学生,按照成绩降序排序
/*
select name,classa,score fron student order by classa,score desc;
create table student(
id int(10) not null auto_increment,
name varchar(30) not null,
classa varchar(30) not null,
score int(10) not null,
primary key(id)
)charset=utf8;
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小高',1,30);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小里',2,40);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小是',3,50);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小上',1,60);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小的',2,70);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小他',3,80);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小的',1,90);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小高',2,50);
INSERT INTO `student`(`id`, `name`, `classa`, `score`) VALUES ('','小高',3,40);
//2.输出每个班和班对应及格人数和不及格人数
select classa,sum(if(score>=60,1,0)) jige,sum(if(score<60,1,0)) bujige from student
group by classa;
*/</span></strong>
sql语句里的if用法实例
最新推荐文章于 2024-06-29 12:10:01 发布