子查询实战

项目介绍

• A公司是一家软件产品销售公司，在北京，上海，深圳，成都，杭州都设有销售部门，其中销售部门分布如下：
• 北京有3个销售部门，分别为bj001，bj002，bj003
• 上海有三个销售部门为：sh001，sh002,sh003
• 深圳有两个销售部门为：sz001，sz002
• 成都有一个销售部门为：cd001
• 杭州有一个销售部门为：hz001
• department表中记录了部门相关的信息
• sales_list表中记录了最近2周各部门的销售订单相关数据

项目需求

• 需求1：在mysql中创建数据库hogwarts_db1 并导入相关数据
• 需求2：计算出各部门最近两周的的总销售业绩，并按业绩由高到低显示
• 需求3：查询出最近两周的销售额超过全公司平均销售额的部门

创建数据库并导入相关数据

• 部门表字段
  • dept_id 部门id
  • city 所在城市
  • manager 部门经理
• 订单表
  • dept_id 部门id
  • order_id 订单号
  • volume 客单价
  • sales_date 销售日期

计算销售业绩并排序

 

# 计算出各部门最近两周的总业绩，并按业绩由高到低排名
SELECT
    order_list.dept_id,
    city,
    manager,
    SUM( volume ) total_volume
FROM
    order_list
    INNER JOIN department ON order_list.dept_id = department.dept_id 
GROUP BY
    order_list.dept_id 
ORDER BY
    SUM( volume ) DESC

查询出最近两周销售额超平均销售额的部门

 

WITH temp_dept AS (
    SELECT
        order_list.dept_id,
        city,
        manager,
        SUM( volume ) total_volume 
    FROM
        order_list
        INNER JOIN department ON order_list.dept_id = department.dept_id 
    GROUP BY
        order_list.dept_id 
    ORDER BY
        SUM( volume ) DESC 
    ) SELECT
    * 
FROM
    temp_dept 
WHERE
    total_volume >(
    SELECT
        AVG( total_volume ) 
FROM
    temp_dept)

知识扩展-视图

 

• 定义：视图是一种虚拟的表，它并不会在你的存储空间复制一份数据，而是对原有数据的一种引用。可以将视图理解为一种存储起来的sql语句
• 视图可以简化多表查询
• 视图也可以用于控制用户权限
• 使用关键词view来创建视图
• 语法：CREATE VIEW [视图名称] AS SELECT…..