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

--1.學(xué)生表 Student(SId, Sname, Sage, Ssex)
SId 學(xué)生編號, Sname 學(xué)生姓名, Sage 出生年月, Ssex 學(xué)生性別
--2.課程表 Course(CId, Cname, TId)
CId 課程編號, Cname 課程名稱, TId 教師編號
--3.教師表 Teacher(TId, Tname)
TId 教師編號, Tname 教師姓名
--4.成績表 SC(SId, CId, score)
SId 學(xué)生編號, CId 課程編號, 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' , '語文' , '02');
insert into Course values('02' , '數(shù)學(xué)' , '01');
insert into Course values('03' , '英語' , '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' , '王五');

成績表 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 "課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
• 1.2 查詢同時存在"01"課程和"02"課程的情況
• 1.3 查詢存在"01"課程但可能不存在"02"課程的情況 (不存在時顯示為 null)
• 1.4 查詢不存在"01"課程但存在"02"課程的情況
• 2. 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號、學(xué)生姓名和平均成績
• 3. 查詢在 SC 表存在成績的學(xué)生信息
• 4. 查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績 (沒成績的顯示為 null)
• 4.1 查有成績的學(xué)生信息
• 5. 查詢「李」姓老師的數(shù)量
• 6. 查詢學(xué)過張三老師授課的同學(xué)的信息
• 7. 查詢沒有學(xué)全所有課程的同學(xué)的信息
• 8. 查詢至少有一門課與學(xué)號為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
• 9. 查詢和" 01 "號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
• 10. 查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
• 11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號，姓名及其平均成績
• 12. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息
• 13. 按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
• 14. 查詢各科成績最高分、最低分和平均分：
• 以如下形式顯示：
• 課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
• （及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90）
• 要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列列，若人數(shù)相同，按課程號升序排列
• 15. 按各科成績進(jìn)行排序，并顯示排名， Score 重復(fù)時保留名次空缺
• 15.1 按各科成績進(jìn)行行排序，并顯示排名， Score 重復(fù)時合并名次
• 16. 查詢學(xué)生的總成績，并進(jìn)行排名，總分重復(fù)時保留名次空缺
• 16.1 查詢學(xué)生的總成績，并進(jìn)行排名，總分重復(fù)時不保留名次空缺
• 17. 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號，課程名稱，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
• 18. 查詢各科成績前三名的記錄
• 19. 查詢每門課程被選修的學(xué)生數(shù)
• 20. 查詢出只選修兩門課程的學(xué)生學(xué)號和姓名
• 21. 查詢男生、女生人數(shù)
• 22. 查詢名字中含有「風(fēng)」字的學(xué)生信息
• 23. 查詢同名同性學(xué)生名單，并統(tǒng)計同名人數(shù)
• 24. 查詢 1990 年年出生的學(xué)生名單
• 25. 查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列
• 26. 查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績
• 27. 查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)
• 28. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒成績，沒選課的情況）
• 29. 查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分?jǐn)?shù)
• 30. 查詢不及格的課程
• 31. 查詢課程編號為 01 且課程成績在 80 分及以上的學(xué)生的學(xué)號和姓名
• 32. 求每門課程的學(xué)生人數(shù)
• 33. 成績不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
• 34. 成績有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
• 35. 查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績
• 36. 查詢每門成績最好的前兩名 (同18題）
• 37. 統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）
• 38. 檢索至少選修兩門課程的學(xué)生學(xué)號
• 39. 查詢選修了全部課程的學(xué)生信息
• 40. 查詢各學(xué)生的年齡，只按年份來算
• 41. 按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一
• 42. 查詢本周過生日的學(xué)生
• 43. 查詢下周過生日的學(xué)生
• 44. 查詢本月過生日的學(xué)生
• 45. 查詢下月過生日的學(xué)生
• 附加題：復(fù)購率計算

1.1 查詢" 01 "課程比" 02 "課程成績高的學(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é)號, 課程號, 成績
from 成績表 as a
where 成績>
    (select avg(成績)
     from 成績表 as b
     where a.課程號=b.課程號
     group by 課程號);

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

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 查詢同時存在"01"課程和"02"課程的情況

同時存在，內(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"課程的情況 (不存在時顯示為 null)

可能不存在其中一項，左/右聯(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"課程說明關(guān)聯(lián)表"01"課程信息為null，但存在"02"課程說明關(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. 查詢平均成績大于等于 60 分的同學(xué)的學(xué)生編號、學(xué)生姓名和平均成績

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

3. 查詢在 SC 表存在成績的學(xué)生信息

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

4. 查詢所有同學(xué)的學(xué)生編號、學(xué)生姓名、選課總數(shù)、所有課程的總成績 (沒成績的顯示

為 null)

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

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

和3有什么區(qū)別

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

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

6. 查詢學(xué)過張三老師授課的同學(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é)號為" 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 "號的同學(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é)過"張三"老師講授的任一門課程的學(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é)號，姓名及其平均成績

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. 按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績

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. 查詢各科成績最高分、最低分和平均分：

以如下形式顯示：

課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率

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

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

-- 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ìn)行排序，并顯示排名， Score 重復(fù)時保留名次空缺

http://m.itdecent.cn/p/476b52ee4f1b

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

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

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

17. 統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號，課程名稱，[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. 查詢各科成績前三名的記錄

各科成績排名，但是不能顯示每組前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，其實就是列出同一門課內(nèi)所有分?jǐn)?shù)比較的情況（http://m.itdecent.cn/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é)號和姓名

-- 多表聯(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)計同名人數(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. 查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時，按課程編號升序排列

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

26. 查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號、姓名和平均成績

嵌套或者聯(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é)生沒成績，沒選課的情況）

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課程成績在 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. 查詢課程編號為 01 且課程成績在 80 分及以上的學(xué)生的學(xué)號和姓名

-- 子查詢/多表聯(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. 成績不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績

-- 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. 成績有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績

35. 查詢不同課程成績相同的學(xué)生的學(xué)生編號、課程編號、學(xué)生成績

36. 查詢每門成績最好的前兩名 (同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)計每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計）

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

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

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é)生的年齡，只按年份來算

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

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

-- 一般都用這個方法精確到天
select SId as 學(xué)生編號, 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ù)將其視為時間部分為“00:00:00”的DATETIME值。
unit參數(shù)是確定(end-begin)的結(jié)果的單位，表示為整數(shù)。 以下是有效單位：

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

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

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

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

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

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

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

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

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

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

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

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

復(fù)購率計算：

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