數(shù)據(jù)表介紹

--1.學(xué)生表 Student(SId, Sname, Sage, Ssex)
SId 學(xué)生編號(hào), Sname 學(xué)生姓名, Sage 出生年月, Ssex 學(xué)生性別
--2.課程表 Course(CId, Cname, TId)
CId 課程編號(hào), Cname 課程名稱, TId 教師編號(hào)
--3.教師表 Teacher(TId, Tname)
TId 教師編號(hào), Tname 教師姓名
--4.成績(jī)表 SC(SId, CId, score)
SId 學(xué)生編號(hào), CId 課程編號(hào), score 分?jǐn)?shù)

學(xué)生表 Student:

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風(fēng)' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '趙六' , '2013-06-13' , '女');
insert into Student values('13' , '孫七' , '2014-06-01' , '女');

科目表 Course:

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '語(yǔ)文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語(yǔ)' , '03');

教師表 Teacher:

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成績(jī)表 SC:

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

題目：

• 1.1 查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
• 1.2 查詢同時(shí)存在"01"課程和"02"課程的情況
• 1.3 查詢存在"01"課程但可能不存在"02"課程的情況 (不存在時(shí)顯示為 null)
• 1.4 查詢不存在"01"課程但存在"02"課程的情況
• 2. 查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)科乎、學(xué)生姓名和平均成績(jī)
• 3. 查詢?cè)?SC 表存在成績(jī)的學(xué)生信息
• 4. 查詢所有同學(xué)的學(xué)生編號(hào)壁畸、學(xué)生姓名、選課總數(shù)茅茂、所有課程的總成績(jī) (沒成績(jī)的顯示為 null)
• 4.1 查有成績(jī)的學(xué)生信息
• 5. 查詢「李」姓老師的數(shù)量
• 6. 查詢學(xué)過(guò)張三老師授課的同學(xué)的信息
• 7. 查詢沒有學(xué)全所有課程的同學(xué)的信息
• 8. 查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
• 9. 查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
• 10. 查詢沒學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名
• 11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)捏萍，姓名及其平均成績(jī)
• 12. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息
• 13. 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)
• 14. 查詢各科成績(jī)最高分空闲、最低分和平均分：
• 以如下形式顯示：
• 課程 ID令杈，課程 name，最高分进副，最低分这揣，平均分，及格率影斑，中等率给赞，優(yōu)良率，優(yōu)秀率
• （及格為>=60矫户，中等為：70-80片迅，優(yōu)良為：80-90，優(yōu)秀為：>=90）
• 要求輸出課程號(hào)和選修人數(shù)皆辽，查詢結(jié)果按人數(shù)降序排列列柑蛇，若人數(shù)相同，按課程號(hào)升序排列
• 15. 按各科成績(jī)進(jìn)行排序驱闷，并顯示排名耻台， Score 重復(fù)時(shí)保留名次空缺
• 15.1 按各科成績(jī)進(jìn)行行排序，并顯示排名空另， Score 重復(fù)時(shí)合并名次
• 16. 查詢學(xué)生的總成績(jī)盆耽，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺
• 16.1 查詢學(xué)生的總成績(jī)扼菠，并進(jìn)行排名摄杂，總分重復(fù)時(shí)不保留名次空缺
• 17. 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱循榆，[100-85]析恢，[85-70]，[70-60]秧饮，[60-0] 及所占百分比
• 18. 查詢各科成績(jī)前三名的記錄
• 19. 查詢每門課程被選修的學(xué)生數(shù)
• 20. 查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名
• 21. 查詢男生映挂、女生人數(shù)
• 22. 查詢名字中含有「風(fēng)」字的學(xué)生信息
• 23. 查詢同名同性學(xué)生名單泽篮，并統(tǒng)計(jì)同名人數(shù)
• 24. 查詢 1990 年年出生的學(xué)生名單
• 25. 查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)降序排列袖肥，平均成績(jī)相同時(shí)咪辱，按課程編號(hào)升序排列
• 26. 查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)
• 27. 查詢課程名稱為「數(shù)學(xué)」椎组，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)
• 28. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒成績(jī)油狂，沒選課的情況）
• 29. 查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名、課程名稱和分?jǐn)?shù)
• 30. 查詢不及格的課程
• 31. 查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分及以上的學(xué)生的學(xué)號(hào)和姓名
• 32. 求每門課程的學(xué)生人數(shù)
• 33. 成績(jī)不重復(fù)寸癌，查詢選修「張三」老師所授課程的學(xué)生中专筷，成績(jī)最高的學(xué)生信息及其成績(jī)
• 34. 成績(jī)有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中蒸苇，成績(jī)最高的學(xué)生信息及其成績(jī)
• 35. 查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)磷蛹、課程編號(hào)、學(xué)生成績(jī)
• 36. 查詢每門成績(jī)最好的前兩名 (同18題）
• 37. 統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）
• 38. 檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
• 39. 查詢選修了全部課程的學(xué)生信息
• 40. 查詢各學(xué)生的年齡溪烤，只按年份來(lái)算
• 41. 按照出生日期來(lái)算味咳，當(dāng)前月日 < 出生年月的月日則，年齡減一
• 42. 查詢本周過(guò)生日的學(xué)生
• 43. 查詢下周過(guò)生日的學(xué)生
• 44. 查詢本月過(guò)生日的學(xué)生
• 45. 查詢下月過(guò)生日的學(xué)生
• 附加題：復(fù)購(gòu)率計(jì)算

1.1 查詢" 01 "課程比" 02 "課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)

直接在原表中不好比較檬嘀，自交

select s.*, c.CId, c.score
from student s left join sc c on s.SId = c.SId 
where s.SId in( select a.SId 
                from (select * from sc where CId = 01) a  inner join  
                     (select * from sc where CId = 02) b 
                on a.SId = b.SId 
                where a.score > b.score 
                group by a.SId );

除了用關(guān)聯(lián)子查詢槽驶，還可以用聯(lián)結(jié)：

select 學(xué)號(hào), 課程號(hào), 成績(jī)
from 成績(jī)表 as a
where 成績(jī)>
    (select avg(成績(jī))
     from 成績(jī)表 as b
     where a.課程號(hào)=b.課程號(hào)
     group by 課程號(hào));

select a.學(xué)號(hào), a.課程號(hào), a.成績(jī)
from 成績(jī)表 a left join 
      (select 課程號(hào), avg(成績(jī)) as 平均成績(jī)
      from 成績(jī)表
      group by 課程號(hào)) b
on a.課程號(hào) = b.課程號(hào)
where a.成績(jī) > b.平均成績(jī);

select emp, sj, date
from empo as a
where date < 
    (select date
     from empo as b
     where a.sj = b.emp
     group by emp);

select a.emp, a.date, b.emp, b.date
from empo a left join empo b
on a.sj = b.emp
where a.date < b.date;

1.2 查詢同時(shí)存在"01"課程和"02"課程的情況

同時(shí)存在，內(nèi)聯(lián)結(jié)

select * 
from  (select * from sc where CId=01) a inner join 
      (select * from sc where CId=02) b 
on a.SId = b.SId

1.3 查詢存在"01"課程但可能不存在"02"課程的情況 (不存在時(shí)顯示為 null)

可能不存在其中一項(xiàng)鸳兽，左/右聯(lián)結(jié)

select * 
from  (select * from sc where CId=01) a left join 
      (select * from sc where CId=02) b 
on a.SId = b.SId;

1.4 查詢不存在"01"課程但存在"02"課程的情況

不存在"01"課程說(shuō)明關(guān)聯(lián)表"01"課程信息為null掂铐，但存在"02"課程說(shuō)明關(guān)聯(lián)表"02"課程信息存在

select * 
from  (select * from sc where CId=01) a right join 
      (select * from sc where CId=02) b 
on a.SId = b.SId
where a.SId is null;

2. 查詢平均成績(jī)大于等于 60 分的同學(xué)的學(xué)生編號(hào)、學(xué)生姓名和平均成績(jī)

select a.SId, b.Sname, avg(a.score) as 平均成績(jī)
from sc a inner join Student b
on a.SId = b.SId
group by a.SId
having avg(a.score) > 60;

3. 查詢?cè)?SC 表存在成績(jī)的學(xué)生信息

select *
from student
where SId in ( select SId from sc where score is not null);

4. 查詢所有同學(xué)的學(xué)生編號(hào)揍异、學(xué)生姓名全陨、選課總數(shù)、所有課程的總成績(jī) (沒成績(jī)的顯示

為 null)

select a.SId, a.Sname, count(b.CId) as 選課總數(shù), sum(b.score) as 總成績(jī)
from student a left join sc b
on a.SId = b.SId
group by a.SId;

4.1 查有成績(jī)的學(xué)生信息

和3有什么區(qū)別

5. 查詢「李」姓老師的數(shù)量

select count(*)
from teacher
where Tname like '李%';

6. 查詢學(xué)過(guò)張三老師授課的同學(xué)的信息

-- 1
select a.*
from student a right join sc b on a.SId = b.SId right join 
     course c on b.CId = c.CId right join teacher d on c.TId = d.TId
where d.Tname = '張三';

-- 2
select s.* 
from sc LEFT JOIN student s 
on sc.SId = s.SId
where CId =(
  select CId 
  from course c 
  where TId =(
       select TId 
       from teacher t 
       where Tname ='張三'));

7. 查詢沒有學(xué)全所有課程的同學(xué)的信息

沒有學(xué)全=排除學(xué)全了的學(xué)生剩下的

select *
from student
where SId not in (
      select SId 
      from sc
      group by SId
      having count(distinct(CId)) = (select count(course.CId) from course));

8. 查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息

select * 
from student
where SId IN ( 
    select SId 
    from sc 
    where SId !=01 and 
          CId in( select CId 
          from sc 
          where SId=01));

至少有一門課相同衷掷，排除和他沒有一門課相同的辱姨、有課的學(xué)生后剩下的學(xué)生

select *
from student
where SId in (select SId from sc where score is not null and SId != '01') and 
      SId not in (select SId from sc where CId not in (select CId from sc where SId = '01'));

9. 查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息

-- 課程數(shù)相同的
select * from student 
where SId in
    (select SId from
        (select * from sc a where CId in (select CId from sc where SId=01))b
    group by SId
        having count(CId) =(select count(CId) from sc c where SId=01))
    and SId !=01;

-- 2
select sid
from sc
where sid != '01'
group by sid 
having group_concat(cid) = (select group_concat(cid) from SC where sid = '01');

group_concat() : https://www.cnblogs.com/rxhuiu/p/9134009.html

10. 查詢沒學(xué)過(guò)"張三"老師講授的任一門課程的學(xué)生姓名

多層嵌套或者多表聯(lián)結(jié)

select Sname
from student
where SId not in (select SId
                  from sc
                  where CId = (select CId
                               from course
                               where TId = (select TId
                                            from teacher
                                            where Tname = '張三'))
                  group by SId);

select Sname
from student
where SId not in 
  (select a.SId
   from sc as a inner join course b on a.CId = b.CId
   inner join teacher c on b.TId = c.TId
   where Tname = '張三');

11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)

select a.SId, a.Sname, avg(b.score)
from student a inner join sc b on a.SId = b.SId
where a.SId in(
    select SId
    from sc
    where score < 60
    group by SId
    having count(*) > 1)
group by SId;

12. 檢索" 01 "課程分?jǐn)?shù)小于 60戚嗅，按分?jǐn)?shù)降序排列的學(xué)生信息

select a.*
from student a inner join sc b on a.SId = b.SId
where b.CId = '01' and b.score < 60
group by b.SId
order by b.score desc;

13. 按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī)

select a.SId, a.CId, a.score, b.avscore
from sc a left join
  (select SId, avg(score) as avscore
   from sc
   group by SId) b on a.SId = b.SId
order by avscore desc;

注意炮叶，1.多表聯(lián)結(jié)后形成的表； 2.自身聯(lián)結(jié)后形成的表（自交）渡处；

14. 查詢各科成績(jī)最高分、最低分和平均分：

以如下形式顯示：

課程 ID祟辟，課程 name医瘫，最高分，最低分旧困，平均分醇份，及格率稼锅，中等率，優(yōu)良率僚纷，優(yōu)秀率

（及格為>=60矩距，中等為：70-80，優(yōu)良為：80-90怖竭，優(yōu)秀為：>=90）

要求輸出課程號(hào)和選修人數(shù)锥债，查詢結(jié)果按人數(shù)降序排列列，若人數(shù)相同痊臭，按課程號(hào)升序排列

-- 1
select a.CId, b.Cname, count(a.SId) as 選修人數(shù), max(a.score) as 最高分, min(score) as 最低分, avg(score) as 平均分, 
       concat( truncate(sum(case when a.score >= 60 then 1 else 0 end)/count(SId)*100,2), '%') as 及格率,
       concat( truncate(sum(case when a.score > 70 and a.score < 80 then 1 else 0 end)/count(SId)*100,2), '%') as 中等率,
       concat( truncate(sum(case when a.score >= 80 and a.score < 90 then 1 else 0 end)/count(SId)*100,2), '%') as 優(yōu)良率,
       concat( truncate(sum(case when a.score >= 90 then 1 else 0 end)/count(SId)*100,2), '%') as 優(yōu)良率
from sc a inner join course b on a.CId = b.CId
group by a.CId
order by count(a.SId) desc, a.CId;

-- 2
select CId, count(SId), max(score), min(score), avg(score),
       sum(及格)/count(SId) as 及格率, 
       sum(中等)/count(SId) as 中等率,
       sum(優(yōu)良)/count(SId) as 優(yōu)良率,
       sum(優(yōu)秀)/count(SId) as 優(yōu)秀率
from (select *,
      case when score>=60  then 1 else 0  end as 及格,
      case when  score>=70 and score<80 then 1 else 0  end as 中等 ,
      case when  score>=80 and score<90 then 1 else 0  end as 優(yōu)良 ,
      case when  score>=90 then 1 else 0  end as 優(yōu)秀 
     from sc) a 
group by CId
order by count(SId) desc, CId

15. 按各科成績(jī)進(jìn)行排序哮肚，并顯示排名， Score 重復(fù)時(shí)保留名次空缺

http://www.reibang.com/p/476b52ee4f1b

15.1 按各科成績(jī)進(jìn)行行排序广匙，并顯示排名允趟， Score 重復(fù)時(shí)合并名次

16. 查詢學(xué)生的總成績(jī)，并進(jìn)行排名鸦致，總分重復(fù)時(shí)保留名次空缺

16.1 查詢學(xué)生的總成績(jī)潮剪，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺

17. 統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào)分唾，課程名稱抗碰，[100-85]，[85-70]鳍寂，[70-60]改含，[60-0] 及所占百分比

select a.CId, b.Cname,
    sum(case when a.score between 85 and 100 then 1 else 0 end) as '[100-85]人數(shù)',
    concat( truncate(sum(case when a.score between 85 and 100 then 1 else 0 end)/count(a.SId)*100,2), '%') as '占比',
    sum(case when a.score < 85 and score >= 70 then 1 else 0 end) as '[85-70]人數(shù)',
    concat( truncate(sum(case when a.score < 85 and score >= 70 then 1 else 0 end)/count(a.SId)*100,2), '%') as '占比',
    sum(case when a.score < 70 and score >= 60 then 1 else 0 end) as '[70-60]人數(shù)',
    concat( truncate(sum(case when a.score < 70 and score >= 60 then 1 else 0 end)/count(a.SId)*100,2), '%') as '占比',
    sum(case when a.score < 60 then 1 else 0 end) as '[60-0]人數(shù)',
    concat( truncate(sum(case when a.score < 60 then 1 else 0 end)/count(a.SId)*100,2), '%') as '占比'
from sc a inner join course b on a.CId = b.CId
group by a.CId;

其中

• case when a.score >=70 then 1 else 0 end可以替換為 if (a.score >=70 , 1, 0);
• truncate( a, 2)表示將a的小數(shù)保留2位；
• concat(b, '%')表示將兩部分合并成一部分迄汛；

18. 查詢各科成績(jī)前三名的記錄

各科成績(jī)排名捍壤，但是不能顯示每組前3條（不能group by 了以后取limit）：

select a.CId, a.score, b.*
from sc a inner join student b on a.SId = b.SId
group by a.CId, a.SId
order by a.CId, a.score desc;

一種是用union（比較繁瑣）:

(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 01 order by score desc limit 3)
union all
(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 02 order by score desc limit 3)
union all
(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 03 order by score desc limit 3);

其他方式，先用自己交自己鞍爱，條件為a.cid = b.cid and a.score < b.score鹃觉，其實(shí)就是列出同一門課內(nèi)所有分?jǐn)?shù)比較的情況（http://www.reibang.com/p/476b52ee4f1b）：

select a.cid, a.sid, a.score
from sc a left join sc b
on a.cid = b.cid and a.score < b.score
group by a.cid, a.sid
having count(b.cid) < 3
order by a.cid;

各步驟比較：

366=108條

19. 查詢每門課程被選修的學(xué)生數(shù)

select CId, count(CId)
from sc 
group by CId;

20. 查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名

-- 多表聯(lián)結(jié)查詢
select a.SId, b.Sname
from sc a inner join student b on a.SId = b.SId
group by a.SId
having count(a.CId) = 2;

-- 嵌套子查詢
select SId,Sname 
from student
where SId in (
      select SId from sc
      group by SId
      having count(CId) = 2);

21. 查詢男生、女生人數(shù)

select Ssex, count(*)
from student
group by Ssex;

22. 查詢名字中含有「風(fēng)」字的學(xué)生信息

select *
from student
where Sname like '%風(fēng)%';

23. 查詢同名同性學(xué)生名單睹逃，并統(tǒng)計(jì)同名人數(shù)

select Sname,count(Sname)
from student 
group by Sname
having count(Sname) > 1;

24. 查詢 1990 年年出生的學(xué)生名單

select *
from student
where Sage like '1990%';

select *
from student
where YEAR(student.Sage)=1990;

25. 查詢每門課程的平均成績(jī)盗扇，結(jié)果按平均成績(jī)降序排列，平均成績(jī)相同時(shí)沉填，按課程編號(hào)升序排列

select CId, avg(score)
from sc
group by CId
order by avg(score) desc, CId;

26. 查詢平均成績(jī)大于等于 85 的所有學(xué)生的學(xué)號(hào)疗隶、姓名和平均成績(jī)

嵌套或者聯(lián)結(jié)

select a.SId, b.Sname, avg(a.score)
from sc a inner join student b
on a.SId = b.SId
group by a.SId
having avg(a.score)>=85;

27. 查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)

select b.Sname, a.score
from sc a inner join student b
on a.SId = b.SId
where a.score < 60 and 
      a.CId = ( select CId
                from course
        where Cname = '數(shù)學(xué)');

28. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒成績(jī)翼闹，沒選課的情況）

select a.Sname, a.SId, b.CId, b.score
from student a left join sc b
on a.SId =  b.SId;

29. 查詢?nèi)魏我婚T課程成績(jī)?cè)?70 分以上的姓名斑鼻、課程名稱和分?jǐn)?shù)

select a.Sname, c.Cname, b.score
from student a inner join sc b on a.SId =  b.SId
     inner join course c on b.CId = c.CId
where b.score > 70;

30. 查詢不及格的課程

select CId
from sc
where score < 60;

沒講清楚具體的意思（課程名字還是ID）

31. 查詢課程編號(hào)為 01 且課程成績(jī)?cè)?80 分及以上的學(xué)生的學(xué)號(hào)和姓名

-- 子查詢/多表聯(lián)結(jié)
select SId, Sname
from student
where SId in
    (select SId
     from sc
     where CId = '01' and score >= 80);

32. 求每門課程的學(xué)生人數(shù)

select CId, count(SId) as 選課人數(shù)
from sc
group by CId;

33. 成績(jī)不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中猎荠，成績(jī)最高的學(xué)生信息及其成績(jī)

-- 1
select b.score, a.*
from student a inner join sc b on a.SId = b.SId 
     inner join course c on c.CId = b.CId
     inner join teacher d on d.TId = c.TId
where Tname = '張三'
order by b.score desc
limit 1;

-- 2
select a.*, b.score
from student a inner join sc b on a.SId = b.SId 
     inner join course c on c.CId = b.CId
     inner join teacher d on d.TId = c.TId
where Tname = '張三'
having max(b.score);

34. 成績(jī)有重復(fù)的情況下坚弱，查詢選修「張三」老師所授課程的學(xué)生中蜀备，成績(jī)最高的學(xué)生信息及其成績(jī)

35. 查詢不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)荒叶、學(xué)生成績(jī)

36. 查詢每門成績(jī)最好的前兩名 (同18題）

(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 01 order by score desc limit 2)
union all
(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 02 order by score desc limit 2)
union all
(select a.CId, a.score, b.*
 from sc a inner join student b on a.SId = b.SId
 where CId = 03 order by score desc limit 2);

37. 統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò) 5 人的課程才統(tǒng)計(jì)）

select CId, count(SId) as 選課人數(shù)
from sc
group by CId
having count(SId) > 5;

38. 檢索至少選修兩門課程的學(xué)生學(xué)號(hào)

select SId
from sc
group by SId
having count(CId) > 1;

39. 查詢選修了全部課程的學(xué)生信息

select a.*
from student a inner join sc b on a.SId = b.SId
group by b.SId
having count(b.CId) = (select count(CId) from course);

40. 查詢各學(xué)生的年齡碾阁，只按年份來(lái)算

select SId, Sname, year(Sage) as 出生年份, year(now())-year(Sage) as 年齡
from student;

41. 按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則些楣，年齡減一

-- 一般都用這個(gè)方法精確到天
select SId as 學(xué)生編號(hào), Sname as 學(xué)生姓名, TIMESTAMPDIFF(YEAR, Sage, CURDATE()) as 學(xué)生年齡
from student;

-- 2
select Sid, Sname,
case when (DATE_FORMAT(NOW(),'%m-%d')- DATE_FORMAT(Sage,'%m-%d')) <0
-- 用date()卻不行
then year(now())-year(Sage)-1
else year(now())-year(Sage) 
end as 年齡
from student;

TIMESTAMPDIFF(unit,begin,end)

TIMESTAMPDIFF函數(shù)返回begin-end的結(jié)果脂凶，其中begin和end是DATE或DATETIME表達(dá)式。
TIMESTAMPDIFF函數(shù)允許其參數(shù)具有混合類型戈毒，例如艰猬，begin是DATE值，end可以是DATETIME值埋市。 如果使用DATE值冠桃，則TIMESTAMPDIFF函數(shù)將其視為時(shí)間部分為“00:00:00”的DATETIME值。
unit參數(shù)是確定(end-begin)的結(jié)果的單位道宅，表示為整數(shù)食听。 以下是有效單位：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

42. 查詢本周過(guò)生日的學(xué)生

select *
from student
where week(Sage) = week(now());

week() 和 weekofyear()的區(qū)別：https://www.yiibai.com/sql/sql-weekofyear-function.html

weekofyear()是一個(gè)兼容性函數(shù)，它等效于WEEK(date,3)

sql日期函數(shù): https://www.yiibai.com/sql/sql-date-functions.html

43. 查詢下周過(guò)生日的學(xué)生

select *
from student
where week(Sage) = week(now())+1;

44. 查詢本月過(guò)生日的學(xué)生

select *
from student
where month(Sage) = month(now());

45. 查詢下月過(guò)生日的學(xué)生

select *
from student
where month(Sage) = month(now())+1;

復(fù)購(gòu)率計(jì)算：

select a.商品ID, 
       count(a.times) as '購(gòu)買人數(shù)', 
       sum(if(a.times >1,1,0)) as '重復(fù)購(gòu)買人數(shù)', 
       sum(if(a.times >1,1,0))/count(a.times) as '復(fù)購(gòu)率'
from (select 商品ID, 用戶ID, count(用戶ID) as times
      from userbehavior
      where 行為類型 = 'buy'
      group by 商品ID, 用戶ID) as a
group by a.商品ID;