1、行转列问题
使用case when 分组如:
select student_id ,
max(case course_id when '1' then score else 0 end ) as '语文',
max(case course_id when '2' then score else 0 end ) as '数学',
max(case course_id when '3' then score else 0 end ) as '英语'
from sc
group by student_id
2、查询所有的
双重否定等于肯定 not exists 如:查询选修了所有课程的同学
select * from student s where not EXISTS
(select * from course c where not exists
(select * from sc where sc.course_id = c.course_id and s.student_id = sc.student_id )
)
3、当表当中关联列有重复时候 但是我们只要其中一行的值的时候
可以使用分组 然后去最小的主键
select * from pg_class where oid in (select min(oid)from pg_class group by relname)
4、所有分数都大于多少的
select * from student where student_id in(
select student_id from sc group by student_id HAVING Min(score)>80 )
5、查询出选了李四所选所有课程的学生信息
双重否定,另外李四的课程
select * from student s
where not exists
#查询出李四的所有课程
(select * from (select sc.* from sc ,student where student.student_id = sc.student_id and student.student_name='李四' ) c
where not exists
(select * from sc where sc.course_id = c.course_id and s.student_id = sc.student_id )
)