SQL
问题描述
有商家表（merchants_underline）、销售表（sales_underline）、退款表（sales_underline）、满意度表（satisfaction_underline）四张表，要求给出 每个商家的总销售额、总退款、平均满意度。
数据
DROP TABLE IF EXISTS merchants_underline;
DROP TABLE IF EXISTS sales_underline;
DROP TABLE IF EXISTS refunds_underline;
DROP TABLE IF EXISTS satisfaction_underline;
-- 创建表
CREATE TABLE merchants_underline (
merchant_id INT PRIMARY KEY,
merchant_name VARCHAR(50),
industry VARCHAR(20)
);
CREATE TABLE sales_underline (
sale_id INT PRIMARY KEY,
merchant_id INT,
sale_amount DECIMAL(10, 2)
);
CREATE TABLE refunds_underline (
refund_id INT PRIMARY KEY,
merchant_id INT,
refund_amount DECIMAL(10, 2)
);
CREATE TABLE satisfaction_underline (
satisfaction_id INT PRIMARY KEY,
merchant_id INT,
satisfaction_score INT
);
-- 插入数据
INSERT INTO merchants_underline (merchant_id, merchant_name, industry)
VALUES (1, '商家 A', '服装'),
(2, '商家 B', '电子产品');
INSERT INTO sales_underline (sale_id, merchant_id, sale_amount)
VALUES (1, 1, 5000.00),
(2, 2, 8000.00),
(3, 1, 4000.00),
(4, 2, 6000.00);
INSERT INTO refunds_underline (refund_id, merchant_id, refund_amount)
VALUES (1, 1, 1000.00),
(2, 2, 1500.00);
INSERT INTO satisfaction_underline (satisfaction_id, merchant_id, satisfaction_score)
VALUES (1, 1, 80),
(2, 2, 90),
(3, 1, 70),
(4, 2, 60);
select * from merchants_underline;
select * from sales_underline;
select * from refunds_underline;
select * from satisfaction_underline;
输出示例
merchant_id merchant_name total_sales_amount total_refund_amount average_satisfaction_score
1 商家 A 9000.00 1000.00 75.00
2 商家 B 14000.00 1500.00 75.00
思路
先求出 总销售额、总退款、平均满意度 任意一个值，然后使用嵌套子查询分别得出另外两个值
SQL实现
select
a.merchant_id
,a.merchant_name
,sum(b.sale_amount) as total_sales_amount -- 总销售额
,total_refund_amount -- 总退款
,average_satisfaction_score -- 平均满意度
from
merchants_underline a
left join
sales_underline b on a.merchant_id = b.merchant_id
left join (
select
a.merchant_id
,sum(c.refund_amount) as total_refund_amount
,average_satisfaction_score
from
merchants_underline a
left join
refunds_underline c on a.merchant_id = c.merchant_id
left join
(
select
a.merchant_id
,round(avg(d.satisfaction_score),2) as average_satisfaction_score
from
merchants_underline a
left join
satisfaction_underline d on a.merchant_id = d.merchant_id
GROUP BY merchant_id
) t3
on a.merchant_id = t3.merchant_id
GROUP BY merchant_id
) t2
on a.merchant_id = t2.merchant_id
group by merchant_id
SQL 实现2
SELECT
m.merchant_id,
m.merchant_name,
total_sales.total_sales_amount AS total_sales_amount,
total_refunds.total_refund_amount AS total_refund_amount,
avg_satisfaction.average_satisfaction_score AS average_satisfaction_score
FROM
merchants_underline m
LEFT JOIN (
SELECT
merchant_id,
SUM(sale_amount) AS total_sales_amount
FROM
sales_underline
GROUP BY
merchant_id
) total_sales
ON m.merchant_id = total_sales.merchant_id
LEFT JOIN (
SELECT
merchant_id,
SUM(refund_amount) AS total_refund_amount
FROM
refunds_underline
GROUP BY
merchant_id
) total_refunds
ON m.merchant_id = total_refunds.merchant_id
LEFT JOIN (
SELECT
merchant_id,
AVG(satisfaction_score) AS average_satisfaction_score
FROM
satisfaction_underline
GROUP BY
merchant_id
) avg_satisfaction
ON m.merchant_id = avg_satisfaction.merchant_id
PS：四表直接 join 结果不对
-- SQL
select
a.merchant_id
,a.merchant_name
,sum(b.sale_amount) as total_sales_amount
,sum(c.refund_amount) as total_refund_amount
,avg(d.satisfaction_score) as average_satisfaction_score
from
merchants_underline a
left join
sales_underline b on a.merchant_id = b.merchant_id
left join
refunds_underline c on a.merchant_id = c.merchant_id
left join
satisfaction_underline d on a.merchant_id = d.merchant_id
GROUP BY merchant_id
-- 结果
merchant_id merchant_name total_sales_amount total_refund_amount average_satisfaction_score
1 商家 A 18000.00 4000.00 75.00
2 商家 B 28000.00 6000.00 75.00
算法
问题描述
通过给定的 序列长度 和 序列，求最小值第一次和最后一次出现的位置
Python实现
# 接收 序列长度 和 序列值
n = int(input())
list = list(map(int,input().split()))
if n!=len(list) :
print("输入元素长度与指定长度不一致，请重新输入")
else:
min_value = min(list) # 获取 序列 最小值
first_idx = None
last_idx = None
for idx,num in enumerate(list):
if num == min_value:
if first_idx is None:
first_idx = idx # 更新 最小值第一次出现的位置
last_idx = idx # 更新 最小值最后一次出现的位置
print(first_idx + 1,last_idx + 1)
