sql 五十题 26-30

#26、查询每门课程被选修的学生数 select count(a.s_id) as count_course , b.c_name from score a left join course b on a.c_id = b.c_id group by a.c_id

书写怎么这么糟糕......

---

#27、查询出只有两门课程的全部学生的学号和姓名 select count(a.c_id),b.* from score a, student b where a.s_id = b.s_id group by a.s_id having count(a.c_id) = 2

---

#查询男生女生人数 select count(s_sex),s_sex from student group by s_sex

666count总是搞不懂应该count谁，用谁分类。

---

-- 29、查询名字中含有"风"字的学生信息 select * from student where s_name like '%风'

注意like 和 = 的区别

---

-- 30、查询同名同性学生名单，并统计同名人数 with t_table as (select count(s_name) over (partition by s_id order by count(s_name)) as count_name, s_name, s_id, s_sex from student group by s_id ) select s_name,s_sex,count(*) from t_table where count_name > 1 group by s_id

明显你写的很复杂，看解析

select a.s_name, a.s_sex, count(*) from student a JOIN student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex GROUP BY a.s_name,a.s_sex

首先两个表连接做笛卡尔积，随后限定条件有【a.s_id != b.s_id】这个条件出来就是异响匹配，你写的时候没想到，然后是两个连接条件，再是【count（*）】是计算做出来的表格所有的行数，也就是只符合条件的总人数。