有两个表,一个班级表class,字段有c_id,c_name
一个学生表student,字段有,s_id ,s_name,c_id(关联班级表)
1,查询s_id,s_name,c_name,学生名字不重复(重复的时,显示编号最小的记录)
2,查询s_id,s_name,c_name,班级人数,条件:班级人数>5的记录
一个学生表student,字段有,s_id ,s_name,c_id(关联班级表)
1,查询s_id,s_name,c_name,学生名字不重复(重复的时,显示编号最小的记录)
2,查询s_id,s_name,c_name,班级人数,条件:班级人数>5的记录
select * from (select t1.s_id,
t1.s_name,
t.c_name,
row_number() over(partition by t1.s_name order by s_id) as cn
from class t, student t1
where t.c_id = t1.c_id) where cn=1;
2,查询s_id,s_name,c_name,班级人数,条件:班级人数>5的记录
select * from (select t1.s_id,
t1.s_name,
t.c_name,
count(s_id) over(partition by t.c_id) as 班级人数
from class t, student t1
where t.c_id = t1.c_id) where 班级人数>5;
select t1.s_id, t1.s_name, t2.c_name
from student t1,
class t2
where t1.c_id = t2.c_id
and t1.s_id in (select min(s_id) from student group by s_name);2select s_id, s_name, c_name, c_count
from student t1,
(select a.c_id, b.c_name, count(a.s_id) as c_count
from student a,
class b
where b.c_id = a.c_id
group by a.c_id, b.c_name
having count(a.s_id) > 5) t2
where t1.c_id = t2.c_id;