超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第四部分40-52）

本文链接：https://blog.csdn.net/qq_48784015/article/details/108421696

40. 交换性别

需求：给定一个 salary 表，有 m = 男性和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

展示效果：

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

create table if not exists 40_salary(id int, name varchar(100), sex char(1), salary int);
Truncate table 40_salary;
insert into 40_salary (id, name, sex, salary) values (1, 'A', 'm', 2500);
insert into 40_salary (id, name, sex, salary) values (2, 'B', 'f', 1500);
insert into 40_salary (id, name, sex, salary) values (3, 'C', 'm', 5500);
insert into 40_salary (id, name, sex, salary) values (4, 'D', 'f', 500);

最终SQL:

UPDATE 40_salary
SET
    sex = CASE sex
               WHEN 'm' THEN 'f'
               ELSE 'm'
          END;

41. 买下所有产品的用户

需求：编写一个 SQL 查询，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

展示效果：

customer_id
1
3

Create table If Not Exists 41_Customer (customer_id int, product_key int);
Create table 41_Product (product_key int);
Truncate table 41_Customer;
insert into 41_Customer (customer_id, product_key) values (1, 5);
insert into 41_Customer (customer_id, product_key) values (2, 6);
insert into 41_Customer (customer_id, product_key) values (3, 5);
insert into 41_Customer (customer_id, product_key) values (3, 6);
insert into 41_Customer (customer_id, product_key) values (1, 6);
Truncate table 41_Product;
insert into 41_Product (product_key) values (5);
insert into 41_Product (product_key) values (6);

最终SQL:

select
	customer_id
from 
	(select
          customer_id,
          count(distinct product_key) as num 
 	 from
          41_Customer
 	 group by
          customer_id) t
join 
    (select
          count(product_key) as num
     from 
          41_Product) m 
on t.num = m.num;

42. 合作过至少三次的演员和导演

需求：编写一个 SQL 查询，查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)

展示效果：

actor_id	director_id
1	1

Create table If Not Exists 42_ActorDirector (actor_id int, director_id int, timestamp int);
Truncate table 42_ActorDirector;
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 0);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 1);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 1, 2);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 3);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (1, 2, 4);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 5);
insert into 42_ActorDirector (actor_id, director_id, timestamp) values (2, 1, 6);

最终SQL:

select 
      actor_id,
      director_id
from 
      42_ActorDirector 
group by 
      actor_id,director_id 
having 
      count(*)>=3;

43. 产品销售分析

需求一：获取产品表 Product 中所有的 产品名称 product name 以及该产品在 Sales 表中相对应的 上市年份 year 和 价格 price。

展示效果：

product_name	year	price
Nokia	2008	5000
Nokia	2009	5000
Apple	2011	9000

Create table 43_Sales (sale_id int, product_id int, year int, quantity int, price int);
Create table 43_Product (product_id int, product_name varchar(10));
Truncate table 43_Sales;
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (1, 100, 2008, 10, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (2, 100, 2009, 12, 5000);
insert into 43_Sales (sale_id, product_id, year, quantity, price) values (7, 200, 2011, 15, 9000);
Truncate table 43_Sales;
insert into 43_Product (product_id, product_name) values (100, 'Nokia');
insert into 43_Product (product_id, product_name) values (200, 'Apple');
insert into 43_Product (product_id, product_name) values (300, 'Samsung');

最终SQL:

select 
      product_name,
      year,
      price
from 
      43_Sales 
inner join 
      43_Product
on
      43_Sales.product_id = 43_Product.product_id;

需求二：按产品 id（product_id ）来统计每个产品的销售总量。

展示效果：

product_id	total_quantity
100	22
200	15

最终SQL:

SELECT
    product_id, 
    SUM(quantity) as total_quantity
FROM
    43_Sales
GROUP BY
    product_id;

需求三：选出每个销售产品的第一年的产品 id、年份、数量和价格。

展示效果：

product_id	first_year	quantity	price
100	2008	10	5000
200	2011	15	9000

最终SQL:

select 
      product_id,
      year as first_year, 
      quantity,
      price
from 
      43_Sales
where 
     (product_id , year) in(
                            select
                                  product_id ,
                                  min(year)
                            from
                                  43_Sales
                            group by
                                  product_id
                            );