软件测试学习笔记丨SQL子查询实战练习

最新推荐文章于 2024-10-01 12:49:02 发布

霍格沃兹测试开发学社测试人社区

最新推荐文章于 2024-10-01 12:49:02 发布

阅读量927

点赞数 22

文章标签：学习笔记 sql 软件测试

本文链接：https://blog.csdn.net/ceshiren_com/article/details/141468225

版权

本文转自测试人社区，原文链接：https://ceshiren.com/t/topic/31953

一、项目介绍

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

二、项目需求

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

三、实战步骤

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

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

3.2 计算销售业绩并排序

# 计算出各部门最近两周的总业绩，并按业绩由高到低排名
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

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

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)

3.4 知识扩展-视图

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

3.5 使用视图简化练习

CREATE VIEW 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);

#查询出最近两周的冠军销售部门

SELECT * FROM temp_dept WHERE total_volume=(SELECT max(total_volume) FROM temp_dept)