SQL语句面试题

1、成绩表(学生名称，科目，成绩)：查询出来所有科目成绩合格的学生，并且获取该学生最高分、最低分、平均分

解题思路：

　　首先要查询出所有成绩不合格的学生 SELECT name FROM scores WHERE score < 60

　　然后排除掉 not in

　　再依据条件进行分组 GROUP BY

　　最后添加查询条件 name,MAX(score) AS highest_score,MIN(score) AS lowest_score,AVG(score) AS average_score

SELECT
name,
MAX(score) AS highest_score,
MIN(score) AS lowest_score,
AVG(score) AS average_score
FROM
scores
WHERE
name NOT IN (
SELECT
name
FROM
scores
WHERE
score < 60
)
GROUP BY
name;

 

2、查询指定用户在过去12个月每个月的支付总额：

SELECT
DATE_FORMAT(payment_date, '%Y-%m') AS month,
SUM(amount) AS total_amount
FROM
payments
WHERE
user_id = '指定用户ID' AND
payment_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY
DATE_FORMAT(payment_date, '%Y-%m')
ORDER BY
DATE_FORMAT(payment_date, '%Y-%m');

 

详解：

DATE_FORMAT(payment_date, '%Y-%m') AS month：将支付日期格式化为YYYY-MM的格式，并命名为month。
SUM(amount) AS total_amount：计算每个月的支付总额，并命名为total_amount。
FROM payments：从payments表中查询数据。
WHERE user_id = '指定用户ID' AND payment_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)：筛选出指定用户在过去12个月内的支付记录。
GROUP BY DATE_FORMAT(payment_date, '%Y-%m')：按月份分组。
ORDER BY DATE_FORMAT(payment_date, '%Y-%m')：按月份排序。