目录
1.计算雇员奖金，若id为偶且名字不以'M'开头、不以'n'结尾，则奖金为工资的80%，否则为15%，返回结构按照id排序，奖金四舍五入为整数：
2.搜索从2022年（包含）以来具有非负收入的customer，customer的id去重，按照id升序返回结果：
3.有Customer、Orders、Seller三张表，搜索在2023年没有任何卖出的所有卖家的名字，按照seller_name降序返回结果：
4.一张配送表，若顾客期望配送日期和下单日期相同或仅在下单后一天，则称该订单为即时订单，否则为计划订单，搜索返回即时订单的占比，保留两位小数：
5.一张销售表，将不同年份的同一天进行求和后返回apples和oranges销售数目的差异，并将sale_date按照('MM-DD')排序返回结果：
6.一张导演演员表，搜索至少合作过4次的演员和导演的id组合(actor_id, director_id)，按合作次数、演员id、导演id降序返回结果：
7.一张电影院座位表，查找所有连续可用的座位，按照seat_id升序返回结果：
8.一张游戏记录表，搜索每个玩家首次登陆的设备id，按玩家id升序返回结果：
9.给Customer、Product、Orders三张表，搜索2023年1月至3月中，每月至少花费100￥客户的id和name，按照id升序返回结果：
10.一张员工培训表，包含公司开设的三门课程、7位员工，搜索该公司所有课程的参加培训人次：
11.一张Traffic表，包含登录信息，搜索从今天2024-5-18起过去30天内（包含30天）每个日期首次登录的用户数，按照日期升序返回结果：
12.给Accounts和Transactions两张表，若一账户连续3月及以上总收入超过最大收入的80%，则该账户可疑，搜索所有可以账户，按照id升序返回结果：
13.给Friendship和Likes两张表，向id=2的用户推荐起朋友们喜欢的页面，不推荐该用户已喜欢的页面，按照页面id升序返回结果，要求去重：
1.计算雇员奖金，若id为偶且名字不以'M'开头、不以'n'结尾，则奖金为工资的80%，否则为15%，返回结构按照id排序，奖金四舍五入为整数：
SQL code：
SELECT employee_id,
CASE
WHEN employee_id % 2 = 0 AND name NOT LIKE 'M%' AND name NOT LIKE '%n' THEN ROUND(salary*0.8, 0) # id为偶数，名字不以‘M’开头不以‘n’结尾
ELSE ROUND(salary*0.15, 0) # 保留0位小数，即取整
END AS bonus # 新命名为bonus
FROM Employees # 指定表
ORDER BY employee_id ASC; # ASC升序 DESC降序2.搜索从2022年（包含）以来具有非负收入的customer，customer的id去重，按照id升序返回结果：
SQL code：
SELECT DISTINCT customer_id # DISTINCT表示去重
FROM Customers # 指定表
WHERE revenue >= 0 and year >= 2022 # 条件
ORDER BY customer_id ASC; # ASC表示升序3.有Customer、Orders、Seller三张表，搜索在2023年没有任何卖出的所有卖家的名字，按照seller_name降序返回结果：
SQL code：
SELECT DISTINCT seller_name # DISTINCT表示去重
FROM Seller # 指定表
WHERE seller_id NOT IN (SELECT DISTINCT seller_id FROM Orders WHERE sale_date LIKE '2023%') # 2023年卖出的卖家id，并且去重
ORDER BY seller_name DESC; # DESC降序4.一张配送表，若顾客期望配送日期和下单日期相同或仅在下单后一天，则称该订单为即时订单，否则为计划订单，搜索返回即时订单的占比，保留两位小数：
SQL code：
SELECT
ROUND(
(SUM(
CASE WHEN order_date = customer_pref_delivery_date OR DATE_ADD(order_date, INTERVAL 1 DAY) = customer_pref_delivery_date THEN 1 # 下单日期=期望配送日期或者期望配送日期后一天
ELSE 0
END) / COUNT(*)) * 100,
2) AS immediate_percentage #ROUND(..., 2)保留两位小数
FROM Delivery; #指定表5.一张销售表，将不同年份的同一天进行求和后返回apples和oranges销售数目的差异，并将sale_date按照('MM-DD')排序返回结果：
SQL code：
SELECT DATE_FORMAT(sale_date, '%m-%d') AS sale_date, # DATE_FORMAT(date, '%m-%d')按照月份-日期格式输出时间
SUM(CASE
WHEN fruit = 'apples' THEN sold_num
ELSE -sold_num # fruit有两个选项，'apples'和'oranges'，所以非'apples'则'oranges'，减去'oranges'的数量
END) AS diff #新命名为diff
FROM Sales
GROUP BY DATE_FORMAT(sale_date, '%m-%d')；# 按照月份-日期进行分组6.一张导演演员表，搜索至少合作过4次的演员和导演的id组合(actor_id, director_id)，按合作次数、演员id、导演id降序返回结果：
SQL code：
SELECT actor_id, director_id
FROM ActorDirector # 指定表
GROUP BY actor_id, director_id # 先按照这两个进行分组
HAVING COUNT(*) >= 4 # 挑选（actor_id，director_id）出现次数超过4次的
ORDER BY COUNT(*) DESC, actor_id DESC, director_id DESC; #DESC为降序，count(*)计算所有行数7.一张电影院座位表，查找所有连续可用的座位，按照seat_id升序返回结果：
SQL code：
SELECT seat_id
FROM Cinema c1 # 将Cinema暂时命名为c1
WHERE free = 1 AND
EXISTS (SELECT 1
FROM Cinema c2 #将Cinema暂时命名为c2
WHERE c2.free = 1
AND ABS(c2.seat_id - c1.seat_id) = 1) #虽然c1和c2都是Cinema的新命名，但是它们对应的数据状态不同，可看作两张表
ORDER BY seat_id ASC;8.一张游戏记录表，搜索每个玩家首次登陆的设备id，按玩家id升序返回结果：
SQL code：
SELECT player_id, device_id
FROM ( SELECT player_id, device_id, event_date, ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS rn FROM Activity ) AS sub #将SELECT的数据命名为sub表
WHERE rn = 1 # 选出rn=1的数据行
ORDER BY player_id; #不指定升序或降序时使用默认值9.给Customer、Product、Orders三张表，搜索2023年1月至3月中，每月至少花费100￥客户的id和name，按照id升序返回结果：
SQL code：
WITH MonthlySpending AS (
SELECT
c.customer_id,
c.name,
MONTH(o.order_date) AS month,
SUM(p.price * o.quantity) AS total_spent
FROM Customers c #Customers命名为c表
JOIN Orders o ON c.customer_id = o.customer_id #以id相等为条件进行连接
JOIN Product p ON o.product_id = p.product_id #以id相等为条件进行连接
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31' #限制时间范围
GROUP BY c.customer_id, c.name, MONTH(o.order_date) #按照id, name, month分组
HAVING SUM(p.price * o.quantity) >= 100 #挑选出价格乘以数量和大于100的组10.一张员工培训表，包含公司开设的三门课程、7位员工，搜索该公司所有课程的参加培训人次：
SQL code：
SELECT COUNT(DISTINCT staff_id) AS staff_nums #去除重复行后计算行数并命名为staff_nums
FROM cultivate_tb
WHERE course IS NOT NULL; #挑选出course不为NULL的行11.一张Traffic表，包含登录信息，搜索从今天2024-5-18起过去30天内（包含30天）每个日期首次登录的用户数，按照日期升序返回结果：
SQL code：
SELECT activity_date, COUNT(DISTINCT user_id) AS user_count
FROM (
SELECT user_id, MIN(activity_date) AS activity_date
FROM Traffic
WHERE activity = 'login'
AND activity_date BETWEEN DATE_SUB('2024-05-28', INTERVAL 30 DAY) AND '2024-05-28'
GROUP BY user_id
) AS first_login
GROUP BY activity_date
ORDER BY activity_date;12.给Accounts和Transactions两张表，若一账户连续3月及以上总收入超过最大收入的80%，则该账户可疑，搜索所有可以账户，按照id升序返回结果：
SQL code：
SELECT DISTINCT account_id
FROM (
SELECT A.account_id,
SUM(CASE WHEN T.type = 'Creditor' THEN T.amount ELSE 0 END) AS total_income,
A.max_income
FROM Accounts A
JOIN Transactions T ON A.account_id = T.account_id
GROUP BY A.account_id, A.max_income, MONTH(T.day)
) AS MonthlyIncome
GROUP BY account_id
HAVING COUNT(CASE WHEN total_income > max_income * 0.8 THEN 1 ELSE NULL END) >= 3
ORDER BY account_id;13.给Friendship和Likes两张表，向id=2的用户推荐起朋友们喜欢的页面，不推荐该用户已喜欢的页面，按照页面id升序返回结果，要求去重：
SQL code：
SELECT DISTINCT l.page_id AS recommended_page
FROM Likes l
JOIN Friendship f ON (l.user_id = f.user1_id OR l.user_id = f.user2_id)
WHERE (f.user1_id = 2 OR f.user2_id = 2)
AND l.page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 2)
ORDER BY recommended_page ASC;
775
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
