MySQL经典（中）

查询没学过"张三"老师讲授的任一门课程的学生姓名

select 
s.sname
from student s
where s.sid not in (select a.sid
from sc a left join course b
on a.cid=b.cid
left JOIN teacher c
on b.tid=c.tid
where c.tname='张三');

查询本周过生日的学

WEEKDAY(date,type) 是一个 Excel 函数，返回代表一周中第几天的数值，是一个1到7（或0到6）之间的整数。语法格式 WEEKDAY(date,type)，其中 date为日期；type表示返值是从1到7还是从0到6，以及从星期几开始计数，如省略则返值为1到7，且从星期日起计。
WEEKDAY 用途：返回某日期的星期数。在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数
return_type为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期日
YEARWEEK(student.Sage) 输出年周比如198953
substr(YEARWEEK(Sage),5,2) 截取周，从第五个开始取两位长度就是周

select *,
substr(YEARWEEK(Sage),5,2) as birth_week, 
substr(YEARWEEK(CURDATE()),5,2) as now_week
from student ;
select *,substr(YEARWEEK(student.Sage),5,2) as birth_week, substr(YEARWEEK(CURDATE()),5,2) as now_week from student having substr(YEARWEEK(student.Sage),5,2)=substr(YEARWEEK(CURDATE()),5,2);

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]及所占百分比

-- 计算计算肯定要分组啊 ，count 计算无论1或0都会计数，但是null不计数就等于sum
-- 解法1
SELECT
    a.cid,
    b.cname,
    sum( IF (score >= 85 AND score <= 100, 1, 0)),
    CONCAT( sum( IF ( score >= 85 AND score <= 100, 1, 0 ))/ count( 1 )* 100, '%' ) 
FROM sc a left JOIN course b
on a.cid=b.cid
GROUP BY a.cid;
-- 解法2
SELECT
a.cid,
    b.cname,
    count(if (score>=85 and score<=100,1,null)),
    CONCAT( count(if (score>=85 and score<=100,1,null))/ count( 1 )* 100, '%' ) 
FROM sc a left JOIN course b
on a.cid=b.cid
GROUP BY a.cid;

按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

-- rank() over 排序并列时候113
-- dense_rank() 112
-- ROW_NUMBER() over() 重复时去重只保留一条数据

select
*,
rank() over(PARTITION by cid order by score desc) as rn
from sc;

-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select
*,
ROW_NUMBER() over(PARTITION by cid order by score desc) as rn
from sc;

查询各科成绩相同的学生的学生编号、课程编号、学生成绩

select *
from sc a left join sc b
on a.sid=b.sid
where a.cid!=b.cid and a.score=b.score 
group by a.sid ,a.cid;

.查询同名同性学生名单，并统计同名人数

-- 方案1
select a.*,
count(1)
from student a inner join student b 
on a.sname=b.sname and a.ssex=b.ssex and a.sid!=b.sid;
-- 方案2
select * 
from student LEFT JOIN 
(select Sname,Ssex,COUNT(*)同名人数 from Student group by Sname,Ssex) as t1 
on student.Sname =t1.Sname and student.Ssex=t1.Ssex 
where t1.同名人数>1;