stf666

分类

/ 注册

全部文章（共55篇）

题解 | 用户订单信息查询

select city ,round(sum(total_amount),2) as total_order_amount from orders o join customers c on o.customer_id = c.customer_id group by city order by t...

2025-10-23

0 28

题解 | 未下单用户统计

select count(distinct uid) as cnt from user_info where uid not in ( select distinct uid from order_log )

2025-10-23

0 20

题解 | 返回顾客名称和相关订单号以及每个订单的总价

select cust_name ,t2.order_num ,sum(quantity * item_price) as OrderTotal /*窗口函数往往适用于明细与汇总，只保留一条总计的用group by*/ from Customers t1 join Orders t2 on t1....

2025-10-22

0 30

题解 | 输出播放量最高的视频

/*给出时间段，计算时刻有关信息，设置时间点标记union all再查询*/ select t2.cid ,round(max(peak_uv),3) as max_peak_uv from( select cid ,sum(t1.change_num) over(partition by t1.c...

2025-10-22

0 27

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

/*要用到的信息 orders :order_date,customer_id,product_id products:product_id,product_name customers:customer_id,customer_name */ select t.customer_id ,custo...

2025-10-21

0 23

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

/*借这道题好好体会cte，明确每一列从哪个表取更不容易出错*/ with details1 as ( /*第一个步骤表要加with,步骤表之间用逗号连接，注意计算结果都要保留两位小数*/ select p.product_id ,round(sum(p.unit_price*o.quant...

2025-10-21

2 27

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

/*确保每个用户最新订单只有一个，两个条件一起约束*/ select order_id ,customer_name ,order_date from orders o join customers c on o.customer_id = c.customer_id where (o.custom...

2025-10-20

0 50

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

select t2.product_id,t2.cnt from( select t1.product_id ,count(*) as cnt from( select substring_index(order_id,"_",-1) as product_id from ord...

2025-10-20

0 40

题解 | 查询订单

select order_id ,customer_name ,order_date from orders o join customers c on o.customer_id = c.customer_id where order_date in ( select max(order...

2025-10-20

0 23

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

/*注意题目是部门和薪资降序排序含义,是先按薪资在部门内排序，再主查询按部门排序*/ select t.department ,t.employee_name ,t.salary from( select department ,employee_name ,salary ,row_number()...

2025-10-20

0 33