SQL实战测试

SQL实战测试

（请写下 SQL 查询语句，不需要展示结果）

表 a

1. **用一条 ** SQL 语句写出每个月，每个销售有多少个客户收入多少

输出结果表头为“月”，“销售”，“客户数”，“收入”

SELECT
  EXTRACT(YEAR FROM Dates) || '-' || EXTRACT(MONTH FROM Dates) AS "月",
  Sales AS "销售",
  COUNT(DISTINCT Customer) AS "客户数",
  SUM(Revenue) AS "收入"
FROM
  A
GROUP BY
  EXTRACT(YEAR FROM Dates) || '-' || EXTRACT(MONTH FROM Dates),
  Sales
ORDER BY
  "月",
  "销售";
  
  这个查询语句做了以下几件事情：

使用 EXTRACT 函数从 sale_date 字段中提取年份和月份，并将其格式化为 "年-月" 的格式，作为 "月" 列。
使用 COUNT(DISTINCT customer_id) 来计算每个销售员每个月有多少个不同的客户。
使用 SUM(amount) 来计算每个销售员每个月的总收入。
使用 GROUP BY 来按年月和销售员分组，以确保我们得到每个月每个销售员的数据。
使用 ORDER BY 来对结果进行排序，首先按月份，然后按销售员。

2. 用一条 SQL 语句查询出 A 和 B 客户的每个月的收入

输出结果表头为“月”，“客户”，“月收入”

SELECT
  EXTRACT(YEAR FROM Dates) || '-' || LPAD(EXTRACT(MONTH FROM Dates), 2, '0') AS "月",
  Customer AS "客户",
  SUM(Revenue) AS "月收入"
FROM
  A
WHERE
  Customer IN ('A', 'B')
GROUP BY
  EXTRACT(YEAR FROM Dates) || '-' || LPAD(EXTRACT(MONTH FROM Dates), 2, '0'),
  Customer
ORDER BY
  "月",
  "客户";
  
 这个查询语句做了以下几件事情：

使用 EXTRACT 函数从 sale_date 字段中提取年份和月份，并将其格式化为 "年-月" 的格式，作为 "月" 列。月份使用 LPAD 函数确保两位数字的格式（例如，01表示1月）。
使用 SUM(amount) 来计算每个客户每个月的总收入。
使用 WHERE 子句来限制查询只包括客户ID为 'A' 或 'B' 的记录。
使用 GROUP BY 来按年月和客户ID分组，以确保我们得到每个月每个客户的数据。
使用 ORDER BY 来对结果进行排序，首先按月份，然后按客户ID。

3. 用一条 SQL 语句查询出总收入在 300 以上的客户

输出结果表头为“客户”，“收入”

SELECT
  Customer AS "客户",
  SUM(Revenue) AS "收入"
FROM
  A
GROUP BY
  Customer
HAVING
  SUM(REVENUE) > 300
ORDER BY
  "收入" DESC;
  
 这个查询语句做了以下几件事情：

使用SUM(amount)来计算每个客户的总收入。
使用GROUP BY customer_id来按客户ID分组，以确保我们得到每个客户的数据。
使用HAVING SUM(amount) > 300来过滤出总收入超过300元的客户。注意，HAVING子句是在数据分组后应用的条件，因此它可以用来过滤聚合函数的结果。
使用ORDER BY "收入" DESC来对结果进行排序，按照收入从高到低排序。

4. **张三，王五为 ** 1 **组，李四，赵六为 ** 2 组，用一条 SQL 写出 1-4 月 1 组和 2 组对应的收入

输出结果表头为：“月”，“组”，“月收入”

SELECT
  EXTRACT(YEAR FROM sale_date) || '-' || LPAD(EXTRACT(MONTH FROM sale_date), 2, '0') AS "月",
  group_id AS "组",
  SUM(amount) AS "月收入"
FROM
  sales
JOIN
  groups ON sales.salesperson = groups.salesperson
WHERE
  EXTRACT(MONTH FROM sale_date) BETWEEN 1 AND 4
GROUP BY
  EXTRACT(YEAR FROM sale_date) || '-' || LPAD(EXTRACT(MONTH FROM sale_date), 2, '0'),
  group_id
ORDER BY
  "月",
  "组";
  
 这个查询语句做了以下几件事情：

使用 EXTRACT 函数从 sale_date 字段中提取年份和月份，并将其格式化为 "年-月" 的格式，作为 "月" 列。月份使用 LPAD 函数确保两位数字的格式（例如，01表示1月）。
使用 SUM(amount) 来计算每个组每个月的总收入。
使用 JOIN 来连接 sales 表和 groups 表，以便我们可以根据销售员的组ID进行分组。
使用 WHERE 子句来限制查询只包括1月到4月的记录。
使用 GROUP BY 来按年月和组ID分组，以确保我们得到每个月每个组的数据。
使用 ORDER BY 来对结果进行排序，首先按月份，然后按组ID。

5. 用一条 SQL 语句查询出每个销售总收入最高的两天

输出结果表头为“销售”，“日期”，“总收入”

SELECT
  salesperson AS "销售",
  sale_date AS "日期",
  amount AS "总收入"
FROM (
  SELECT
    salesperson,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount DESC) AS rn
  FROM
    sales
) t
WHERE
  rn <= 2
ORDER BY
  "销售",
  "日期";
  
 这个查询语句做了以下几件事情：

使用 ROW_NUMBER() 窗口函数来为每个销售员的收入按降序排列，为每天的收入分配一个行号。
使用 PARTITION BY salesperson 来确保行号是在每个销售员的记录中独立分配的。
使用 ORDER BY amount DESC 来确保收入最高的天有最小的行号。
从子查询中选择行号小于或等于2的记录，这将给我们每个销售员收入最高的两天。
使用 ORDER BY "销售", "日期" 来对结果进行排序，首先按销售员，然后按日期

​

表 b

6. 使用表****A 和表 B ，用一条 SQL 语句查询出每个客户的总收入

输出结果表头为“客户”，“客户 id ”，“性别”，“总收入”

SELECT
  B.Customer AS "客户",
  B.Customer_id AS "客户ID",
  B.Sexual AS "性别",
  SUM(A.Revenue) AS "总收入"
FROM
   B
JOIN
   A ON B.customer = A.customer
GROUP BY
  B.Customer,
  B.Customer_id,
  B.Sexual
ORDER BY
  "总收入" DESC;
  
 这个查询语句做了以下几件事情：

使用JOIN语句来连接表A和表B，通过customer_id字段将两个表关联起来。
使用SUM(B.sale_amount)来计算每个客户的总收入。
使用GROUP BY来按客户姓名、客户ID和性别分组，以确保我们得到每个客户的数据。
使用ORDER BY "总收入" DESC来对结果进行排序，按照总收入从高到低排序。

建表信息与查询测试结果
表A：

--drop table A;
CREATE TABLE A (
    Dates	VARCHAR(512),
    Sales	VARCHAR(512),
    Customer	VARCHAR(512),
    Revenue 	INT
);

INSERT INTO A (Dates, Sales, Customer, Revenue ) VALUES (TO_DATE('2019/1/1', 'YYYY-MM-DD'), '张三', 'A', '10 ');
INSERT INTO A (Dates, Sales, Customer, Revenue ) VALUES (TO_DATE('2019/1/5', 'YYYY-MM-DD'), '张三', 'A', '18');

表B：

--drop table B;
CREATE TABLE B (
    Customer	VARCHAR(512),
    Customer_id	VARCHAR(512),
    Sexual 	VARCHAR(512)
);

INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('A', '00001', '男');
INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('B', '00002', '男');
INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('C', '00003', '女');
INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('D', '00004', '男');
INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('E', '00005', '女');
INSERT INTO B (Customer, Customer_id, Sexual ) VALUES ('F', '00006', '女');

查询结果展示：
1.
2.
6.