（十四）数据库 | SQL案例分析实战

-- 左右滑动查看完整代码-- 查询每年度销售利润前三的业务信息，要求返回FIELDS: year, country, product, profit和利润排名-- 结果集按年份升序和利润降序排列，若同意年份利润相同，则按国家升序排列

WITH temptable AS   (  SELECT year, country, product, profit,  RANK() OVER(partition by year order by profit desc) AS 'profit_ranking'  FROM sales  )SELECT   * FROM   temptableWHERE   profit_ranking <= 3ORDER BY   year, profit DESC, country;

-- 切换数据库并查看sc和course表USE school;SELECT * FROM sc LEFT JOIN course ON sc.cid = course.CId;

-- 查询每门课程的选课人数和成绩情况，要求返回各分段人数所占比例

SELECT sc.CId, course.Cname, COUNT(sc.SId) AS 'Stotal', SUM(IF(score >= 60, 1, 0))/COUNT(sc.SId) AS '[60, 100]',SUM(IF(score >= 60 and score < 70, 1, 0))/COUNT(sc.SId) AS '[60, 70)',SUM(IF(score >= 70 and score < 80, 1, 0))/COUNT(sc.SId) AS '[70, 80)',SUM(IF(score >= 80 and score < 90, 1, 0))/COUNT(sc.SId) AS '[80, 90)',SUM(IF(score >= 90, 1, 0))/COUNT(sc.SId) AS '[90, 100]'FROM   course, scWHERE   sc.CId = course.CIdGROUP BY   sc.CIdORDER BY   Stotal, sc.CId;

-- 查询每门课程得分最高的两名学生的ID，要求返回字段: SId, CId, score和成绩排名-- 结果集按照课程号升序和成绩降序排列

WITH temptable AS (SELECT SId, CId, score, RANK() over(partition by CId order by score DESC) AS 'score_ranking'FROM sc)SELECT   *FROM   temptableWHERE   score_ranking <= 2ORDER BY   CId, score DESC;

-- 切换数据库并查看sale和course表USE jianxingjinke;SELECT * FROM sale LEFT JOIN course ON sale.course_id = course.id;

-- 查询每门课程的销售情况，要求返还FIELDS: 课程名称和销售金额 -- 结果集按照销售金额降序排列

WITH tt AS (SELECT     sale.course_id, name, COUNT(*) * price AS totalFROM    sale        LEFT JOIN    course ON sale.course_id = course.idGROUP BY sale.course_id)SELECT name, totalFROM ttORDER BY total DESC;