当前位置：首页 > news >正文

MySQL高阶2051-商店中每个成员的级别

news 2025/5/15 5:18:39

题目

准备数据

分析数据

实现

总结

题目

一个商店想对其成员进行分类。有三个层次:

"钻石": 如果转换率 大于或等于 80.
"黄金": 如果转换率 大于或等于 50 且小于 80.
"白银": 如果转化率小于 50.
"青铜": 如果该成员从未访问过该商店。

成员的 转化率 为 (100 * 该会员的购买总数) / 该成员的总访问次数.

编写一个 SQL 来查询每个成员的 id、名称和类别。

以 任意顺序 返回结果表。

准备数据

Create table If Not Exists Members (member_id int, name varchar(30))
Create table If Not Exists Visits (visit_id int, member_id int, visit_date date)
Create table If Not Exists Purchases (visit_id int, charged_amount int)Truncate table Membersinsert into Members (member_id, name) values ('9', 'Alice')insert into Members (member_id, name) values ('11', 'Bob')insert into Members (member_id, name) values ('3', 'Winston')insert into Members (member_id, name) values ('8', 'Hercy')insert into Members (member_id, name) values ('1', 'Narihan')Truncate table Visitsinsert into Visits (visit_id, member_id, visit_date) values ('22', '11', '2021-10-28')insert into Visits (visit_id, member_id, visit_date) values ('16', '11', '2021-01-12')insert into Visits (visit_id, member_id, visit_date) values ('18', '9', '2021-12-10')insert into Visits (visit_id, member_id, visit_date) values ('19', '3', '2021-10-19')insert into Visits (visit_id, member_id, visit_date) values ('12', '11', '2021-03-01')insert into Visits (visit_id, member_id, visit_date) values ('17', '8', '2021-05-07')insert into Visits (visit_id, member_id, visit_date) values ('21', '9', '2021-05-12')Truncate table Purchasesinsert into Purchases (visit_id, charged_amount) values ('12', '2000')insert into Purchases (visit_id, charged_amount) values ('18', '9000')insert into Purchases (visit_id, charged_amount) values ('17', '7000')

members表

visits表

purchases表

分析数据

第一步:将三个表进行连接

select *
from members m left join visits v on m.member_id = v.member_idleft join purchases p on p.visit_id = v.visit_id;

第二步:对成员进行分类

select m.member_id,name,(casewhen count(v.visit_id) = 0 then 'Bronze'when count(p.visit_id) * 100 / count(v.visit_id) < 50 then 'Silver'when count(p.visit_id) * 100 / count(v.visit_id) < 80 then 'Gold'else 'Diamond'end) as categoryfrom members m left join visits v on m.member_id = v.member_idleft join purchases p on p.visit_id = v.visit_id
group by m.member_id, name;

实现

select m.member_id,name,(casewhen count(v.visit_id) = 0 then 'Bronze'when count(p.visit_id) * 100 / count(v.visit_id) < 50 then 'Silver'when count(p.visit_id) * 100 / count(v.visit_id) < 80 then 'Gold'else 'Diamond'end) as categoryfrom members m left join visits v on m.member_id = v.member_idleft join purchases p on p.visit_id = v.visit_id
group by m.member_id, name;