34,576
社区成员
发帖
与我相关
我的任务
分享
use tempdb;
/*
create table stud_grade
(
stud_id nvarchar(10) not null,
name nvarchar(10) not null,
course_id int not null,
grade int not null
);
insert into stud_grade(stud_id,name,course_id,grade)
values
('01','aa',1,90),
('01','aa',2,80),
('02','bb',1,70),
('02','bb',2,95),
('03','cc',1,92),
('03','cc',2,90),
('04','dd',1,94);
*/
--显示 课程1的成绩 大于 课程2的成绩 的同学的name,stud_id, course_id
select *
from
(
select stud_id,name,
MAX(case when course_id = 1 then grade end) as [课程1的成绩],
MAX(case when course_id = 2 then grade end) as [课程2的成绩]
from stud_grade
group by stud_id,name
) as t
where t.课程1的成绩 > t.课程2的成绩;
select a.stud_id,a.name
from stud_grade a,stud_grade b
where a.course_id = 1 and b.course_id = 2
and a.stud_id = b.stud_id
and a.grade > b.grade