纯粹爱刷题294408346

分类

/ 注册

全部文章（共25篇）

题解 | 每个顾客购买的最新产品名称

WITH last_order AS( --找到by customer的最后一个商品，该表的两列都整理为外键 SELECT product_id,customer_id FROM ( SELECT *, rank() ...

2025-04-11

0 37

题解 | 各个部门实际平均薪资和男女员工实际平均薪资

WITH dep AS( SELECT staff_id,department,staff_gender FROM staff_tb ) SELECT b.department, round(avg(a.normal_salary-a.dock_salary),2)...

2025-04-08

0 43

题解 | 统计每个产品的销售情况

WITH order_by_product_by_month AS( SELECT product_id, avg(month_quantity) AS avg_monthly_quantity, --这一步不需要哈，理解错题意了，但是如果有这一步就更难 ...

2025-04-07

0 33

题解 | 每个顾客最近一次下单的订单信息

WITH order_last AS( SELECT order_id,customer_id, order_date, rank() over (partition by customer_id order by order_date DES...

2025-04-07

0 37

题解 | 商品id数据清洗统计

SELECT substring_index(order_id,'_',-1) AS product_id, #分割出来下划线后的字符串 count(*) AS cnt FROM order_log GROUP BY substring_index(order_id,'_',-1);

2025-04-05

0 30

题解 | 查询订单

WITH last_order AS ( SELECT *, rank() over (partition by customer_id order by order_date DESC) AS rk FROM orders ), last_orde...

2025-04-05

0 54

题解 | top2的薪水排名

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, rank() over (partition by depar...

2025-04-03

0 36

题解 | 每个部门薪资排名前两名员工

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, rank() over (partition by depar...

2025-04-03

1 37

题解 | 每个客户的账户总金额

select customer_id, sum(balance) as sum_balance from account group by customer_id order by sum(balance) desc;

2025-04-02

0 36

题解 | 商品销售排名

SELECT b.product_name, CAST(SUM(IF(a.step='select',1,0)*b.price) AS signed) AS amount --select即支付，因为有payment method FROM user_client_log AS a...

2025-04-01

2 41