在思考的六边形战士很想去旅行

分类

/ 注册

全部文章（共90篇）

题解 | 更新用户积分信息？

# 先用where条件子句过滤掉order_tb表所有金额不超过100的订单 # 另外，表关联以order_tb表为主，这样就不会把uservip_tb表未满足要求的客户关联进来 # 如果在select后用if(,,)去判断，不严密，会出错 # 因为执行顺序是from-where-group-sel...

2025-08-01

1 32

题解 | 统计用户获得积分

select user_id, sum(floor(timestampdiff(minute,visit_time,leave_time)/10)) as point from visit_tb group by user_id order by point desc ;

2025-08-01

1 36

题解 | 统计员工薪资扣除比例

select a.staff_id, a.staff_name, concat(round(b.dock_salary*100/b.normal_salary,1),'%') as dock_ratio from staff_tb a left join salary...

2025-08-01

1 37

题解 | 统计用户从访问到下单的转化率

select date(ot.order_time) as date, concat(round(count(distinct ot.user_id)*100/count(distinct vt.user_id),1),'%') as cr from visit_tb vt...

2025-08-01

1 25

题解 | 返回产品名称和每一项产品的总订单数

# count(列名)，某行为Null，会记为零；count(*)的话，除非是空表，即便是Null也会计入（计数加一行）。 select p.prod_name, count(oi.order_num) as orders from Products p left j...

2025-07-31

1 29

题解 | 确定最佳顾客的另一种方式（二）

select c.cust_name, round(sum(oi.item_price*oi.quantity),3) as total_price from Orders o inner join OrderItems oi on o.order_num = oi....

2025-07-31

1 41

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

select c.cust_name, os.order_num, sum(os.quantity * os.item_price) OrderTotal from Orders o join OrderItems os on os.order_num = o...

2025-07-31

1 40

题解 | 从 Products 表中检索所有的产品名称以及对应的销售总数

# where子句放在子查询内 select prod_name, (select sum(quantity) from OrderItems b where a.prod_id = b.prod_id group by prod_id) as quant_sold ...

2025-07-31

1 24

题解 | 返回每个顾客不同订单的总金额

# 直接表关联+分组，用易于理解的方式 select cust_id, sum(total_price) as total_ordered from ( select -- 查询范围是关联后的大表，可以选择各个关联表进入大表的列名或进行...

2025-07-31

1 34

题解 | 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

# 多重子查询嵌套 select cust_email from Customers where cust_id in ( select cust_id from Orders where order_num in (select order_num from OrderItems where...

2025-07-30

1 28