SQL题目

题目1

编写一个 SQL 查询，查询总销售额最高的销售者，如果有并列的，就都展示出来，以任意顺序返回结果表。

drop table Product;
Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int);
Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int);

Truncate table Product;
insert into Product (product_id, product_name, unit_price) values (‘1’, ‘S8’, ‘1000’);
insert into Product (product_id, product_name, unit_price) values (‘2’, ‘G4’, ‘800’);
insert into Product (product_id, product_name, unit_price) values (‘3’, ‘iPhone’, ‘1400’);

Truncate table Sales;
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (‘1’, ‘1’, ‘1’, ‘2019-01-21’, ‘2’, ‘2000’);
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (‘1’, ‘2’, ‘2’, ‘2019-02-17’, ‘1’, ‘800’);
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (‘2’, ‘2’, ‘3’, ‘2019-06-02’, ‘1’, ‘800’);
insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values (‘3’, ‘3’, ‘4’, ‘2019-05-13’, ‘2’, ‘2800’);
试题说明：

![在这里插入图片描述](https://img-blog.csdnimg.cn/c15aed3c9bd24dd899bbd1139901fbca.png
正确答案：
无

答案解析：
-- 步骤一：按照seller_id分组计算他们的销售额
SELECT seller_id, SUM(price) as total
FROM Sales
GROUP BY seller_id;

-- 步骤二：按照seller_id分组计算他们的销售额最大值
SELECT MAX(total)
FROM (
         SELECT seller_id, SUM(price) as total
         FROM Sales
         GROUP BY seller_id
     ) t1;

-- 步骤三：从原表里面找最大金额人的sellerid
SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) = (SELECT MAX(total)
                     FROM (
                              SELECT seller_id, SUM(price) as total
                              FROM Sales
                              GROUP BY seller_id
                          ) t1
);


SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price)=(SELECT MAX(total) FROM
    (
    SELECT seller_id, SUM(price) as total
    FROM Sales
    GROUP BY seller_id
    ) t1
)

题目2

Employee 表保存了一年内的薪水信息，请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算），结果请按 Id 升序，然后按 Month 降序显示。

建表语句：

drop table Employee;
Create table If Not Exists Employee (id int, month int, salary int);
Truncate table Employee;
insert into Employee (id, month, salary) values (‘1’, ‘1’, ‘20’);
insert into Employee (id, month, salary) values (‘2’, ‘1’, ‘20’);
insert into Employee (id, month, salary) values (‘1’, ‘2’, ‘30’);
insert into Employee (id, month, salary) values (‘2’, ‘2’, ‘30’);
insert into Employee (id, month, salary) values (‘3’, ‘2’, ‘40’);
insert into Employee (id, month, salary) values (‘1’, ‘3’, ‘40’);
insert into Employee (id, month, salary) values (‘3’, ‘3’, ‘60’);
insert into Employee (id, month, salary) values (‘1’, ‘4’, ‘60’);
insert into Employee (id, month, salary) values (‘3’, ‘4’, ‘70’);
insert into Employee (id, month, salary) values (‘1’, ‘7’, ‘90’);
insert into Employee (id, month, salary) values (‘1’, ‘8’, ‘90’);
试题说明：

-- 步骤一:按照Id分组，并且获取月份的最大值
SELECT Id, MAX(Month) FROM Employee GROUP BY Id;
-- 步骤二:获取不是最大月份的其余月份
SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id);
-- 步骤三:两张表关联，获取两月差值小于3的数据
SELECT *
FROM (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t1
         JOIN (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t2
              ON t1.Id = t2.Id AND t1.Month >= t2.Month AND t1.Month - t2.month < 3;
-- 步骤四:两张表关联，获取两月差值小于3的数据，针对id和表1的月份分组降序排列
SELECT *
FROM (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t1
         JOIN (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t2
              ON t1.Id = t2.Id AND t1.Month >= t2.Month AND t1.Month - t2.month < 3
GROUP BY t1.Id, t1.Month
ORDER BY t1.Id, t1.Month DESC;
-- 步骤五：按照近三个月累计求和
SELECT t1.Id, t1.Month, SUM(t2.Salary) Salary
FROM (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t1
         JOIN (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t2
              ON t1.Id = t2.Id AND t1.Month >= t2.Month AND t1.Month - t2.month < 3
GROUP BY t1.Id, t1.Month
ORDER BY t1.Id, t1.Month DESC
方法一：先除去每个员工的最近一个月数据，接着用两个相同的表（上述得到的表），将id相同，月份在近三个月（月份小，但相差小于3）的数据连接起来，最后按id和月份分组，求和即可（记得排序）。

SELECT t1.Id,t1.Month, SUM(t2.Salary) Salary
FROM (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t1 JOIN (SELECT * FROM Employee WHERE (Id, Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)) t2
ON t1.Id=t2.Id AND t1.Month>=t2.Month AND t1.Month-t2.month<3
GROUP BY t1.Id,t1.Month
ORDER BY t1.Id, t1.Month DESC


方法二：先连接然后再筛掉最大月份

SELECT t1.Id,t1.Month, SUM(t2.Salary) Salary
FROM Employee t1 JOIN Employee t2
ON t1.Id=t2.Id AND t1.Month>=t2.Month AND t1.Month-t2.month<3
WHERE (t2.Id, t2.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id) AND (t1.Id, t1.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)
GROUP BY t1.Id,t1.Month
ORDER BY t1.Id, t1.Month DESC

题目3

编写一条SQL语句，查询每种性别在每一天的总分，返回按 gender 和 day 对查询结果升序排序 的结果。
建表语句：
drop table Scores;
Create table If Not Exists Scores (player_name varchar(20), gender varchar(1), day date, score_points int);
Truncate table Scores;
insert into Scores (player_name, gender, day, score_points) values (‘Aron’, ‘F’, ‘2020-01-01’, ‘17’);
insert into Scores (player_name, gender, day, score_points) values (‘Alice’, ‘F’, ‘2020-01-07’, ‘23’);
insert into Scores (player_name, gender, day, score_points) values (‘Bajrang’, ‘M’, ‘2020-01-07’, ‘7’);
insert into Scores (player_name, gender, day, score_points) values (‘Khali’, ‘M’, ‘2019-12-25’, ‘11’);
insert into Scores (player_name, gender, day, score_points) values (‘Slaman’, ‘M’, ‘2019-12-30’, ‘13’);
insert into Scores (player_name, gender, day, score_points) values (‘Joe’, ‘M’, ‘2019-12-31’, ‘3’);
insert into Scores (player_name, gender, day, score_points) values (‘Jose’, ‘M’, ‘2019-12-18’, ‘2’);
insert into Scores (player_name, gender, day, score_points) values (‘Priya’, ‘F’, ‘2019-12-31’, ‘23’);
insert into Scores (player_name, gender, day, score_points) values (‘Priyanka’, ‘F’, ‘2019-12-30’, ‘17’);

试题说明：

答案解析：简单的道理，就是要对性别相同且日期在之前（包括今天）的得分相加。
SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores s1 JOIN Scores s2 ON s1.gender = s2.gender AND s1.day >= s2.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day