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

最新推荐文章于 2024-06-22 19:36:08 发布
最新推荐文章于 2024-06-22 19:36:08 发布
阅读量209 收藏 1
点赞数
文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_48784015/article/details/108421802
版权

66. Page Recommendations

需求一：Write an SQL query to recommend pages to the user with user_id = 1 using the pages that your friends liked. It should not recommend pages you already liked. Return result table in any order without duplicates.

展示效果：

recommended_page
23
24
56
33
77
Create table If Not Exists 66_Friendship (user1_id int, user2_id int);
Create table If Not Exists 66_Likes (user_id int, page_id int);
Truncate table 66_Friendship;
insert into 66_Friendship (user1_id, user2_id) values (1, 2);
insert into 66_Friendship (user1_id, user2_id) values (1, 3);
insert into 66_Friendship (user1_id, user2_id) values (1, 4);
insert into 66_Friendship (user1_id, user2_id) values (2, 3);
insert into 66_Friendship (user1_id, user2_id) values (2, 4);
insert into 66_Friendship (user1_id, user2_id) values (2, 5);
insert into 66_Friendship (user1_id, user2_id) values (6, 1);
Truncate table 66_Likes;
insert into 66_Likes (user_id, page_id) values (1, 88);
insert into 66_Likes (user_id, page_id) values (2, 23);
insert into 66_Likes (user_id, page_id) values (3, 24);
insert into 66_Likes (user_id, page_id) values (4, 56);
insert into 66_Likes (user_id, page_id) values (5, 11);
insert into 66_Likes (user_id, page_id) values (6, 33);
insert into 66_Likes (user_id, page_id) values (2, 77);
insert into 66_Likes (user_id, page_id) values (3, 77);
insert into 66_Likes (user_id, page_id) values (6, 88);

解释：

User one is friend with users 2, 3, 4 and 6.

Suggested pages are 23 from user 2, 24 from user 3, 56 from user 3 and 33 from user 6.

Page 77 is suggested from both user 2 and user 3.
Page 88 is not suggested because user 1 already likes it.

最终SQL:

select 
      distinct page_id as recommended_page
from 
      66_Likes,
      66_friendship
where 
      page_id not in(select page_id from 66_likes where user_id=1)
      and
      user_id in (select user1_id from 66_friendship where user2_id=1) 
      or
      user_id in (select user2_id from 66_friendship where user1_id=1);

67. All People Report to the Given Manager

需求：用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。由于公司规模较小，经理之间的间接关系不超过 3 个经理。可以以任何顺序返回的结果，不需要去重。

展示效果：

employee_id
2
4
7
77
Create table If Not Exists 67_Employees (employee_id int, employee_name varchar(30), manager_id int);
Truncate table 67_Employees;
insert into 67_Employees (employee_id, employee_name, manager_id) values (1, 'Boss', 1);
insert into 67_Employees (employee_id, employee_name, manager_id) values (3, 'Alice', 3);
insert into 67_Employees (employee_id, employee_name, manager_id) values (2, 'Bob', 1);
insert into 67_Employees (employee_id, employee_name, manager_id) values (4, 'Daniel', 2);
insert into 67_Employees (employee_id, employee_name, manager_id) values (7, 'Luis', 4);
insert into 67_Employees (employee_id, employee_name, manager_id) values (8, 'John', 3);
insert into 67_Employees (employee_id, employee_name, manager_id) values (9, 'Angela', 8);
insert into 67_Employees (employee_id, employee_name, manager_id) values (77, 'Robert', 1);

提示：

The head of the company is the employee with employee_id 1.

The employees with employee_id 2 and 77 report their work directly to the head of the company.

The employee with employee_id 4 report his work indirectly to the head of the company 4 --> 2 --> 1.

The employee with employee_id 7 report his work indirectly to the head of the company 7 --> 4 --> 2 --> 1.

The employees with employee_id 3, 8 and 9 don’t report their work to head of company directly or indirectly.

最终SQL:

select
      employee_id EMPLOYEE_ID
from 
      67_Employees
where 
      manager_id=1 and 
      employee_id!=1
union
select
      a1.employee_id
from 
      67_Employees a1,
     (select 
            employee_id
      from
            67_Employees
      where
            manager_id=1 and
            employee_id!=1
     ) a
where
     manager_id=a.employee_id
union
select 
     a2.employee_id
from 
     67_Employees a2,
    (select
           a1.employee_id employee_id
    from 
           67_Employees a1,
           (select 
                  employee_id
            from
                  67_Employees
            where
                  manager_id=1 and
                  employee_id!=1
           ) a
    where 
           manager_id=a.employee_id
    ) a3
where 
    manager_id=a3.employee_id
order by 
    employee_id;

68. 学生们参加各科测试的次数

需求：写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

展示效果：

+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+

建表语句：

Create table If Not Exists 68_Students (student_id int, student_name varchar(20));
Create table If Not Exists 68_Subjects (subject_name varchar(20));
Create table If Not Exists 68_Examinations (student_id int, subject_name varchar(20));
Truncate table 68_Students;
insert into 68_Students (student_id, student_name) values ('1', 'Alice');
insert into 68_Students (student_id, student_name) values ('2', 'Bob');
insert into 68_Students (student_id, student_name) values ('13', 'John');
insert into 68_Students (student_id, student_name) values ('6', 'Alex');
Truncate table 68_Subjects;
insert into 68_Subjects (subject_name) values ('Math');
insert into 68_Subjects (subject_name) values ('Physics');
insert into 68_Subjects (subject_name) values ('Programming');
Truncate table 68_Examinations;
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Programming');
insert into 68_Examinations (student_id, subject_name) values ('13', 'Physics');
insert into 68_Examinations (student_id, subject_name) values ('2', 'Math');
insert into 68_Examinations (student_id, subject_name) values ('1', 'Math');

最终sql：

SELECT 
     a.student_id,
     a.student_name,
     b.subject_name,
     COUNT(e.subject_name) AS attended_exams
FROM 
     68_Students a 
CROSS JOIN
     68_Subjects b
LEFT JOIN
     68_Examinations e 
ON 
   a.student_id = e.student_id 
   AND
   b.subject_name = e.subject_name
GROUP BY 
   a.student_id, b.subject_name
ORDER BY
   a.student_id, b.subject_name;

69. 找到连续区间的开始和结束数字

需求：编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

展示效果：

+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+

建表语句：

Create table If Not Exists 69_Logs (log_id int);
Truncate table 69_Logs;
insert into 69_Logs (log_id) values ('1');
insert into 69_Logs (log_id) values ('2');
insert into 69_Logs (log_id) values ('3');
insert into 69_Logs (log_id) values ('7');
insert into 69_Logs (log_id) values ('8');
insert into 69_Logs (log_id) values ('10');

最终sql：

-- 方法一
SELECT
    min(log_id) start_id,
    max(log_id) end_id
FROM
	(SELECT
		log_id,
		CASE WHEN @id = log_id - 1 THEN @num := @num
		ELSE @num := @num + 1
		END num, @id := log_id
	 FROM 
         69_Logs,
         (SELECT @num := 0, @id := NULL) a
	) x
GROUP BY num;


-- 方法二
SELECT
    MIN(log_id) start_id,
    MAX(log_id) end_id
FROM
    (SELECT
        log_id, 
        log_id - row_number() OVER(ORDER BY log_id) as diff
     FROM 
        69_Logs ) t
GROUP BY diff;

70. 不同国家的天气类型

需求：写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

天气类型的定义如下：当 weather_state 的平均值小于或等于15返回 Cold，当 weather_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。

展示效果：

+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA          | Cold         |
| Austraila    | Cold         |
| Peru         | Hot          |
| China        | Warm         |
| Morocco      | Hot          |
+--------------+--------------+

建表语句：

Create table If Not Exists 70_Countries (country_id int, country_name varchar(20));
Create table If Not Exists 70_Weather (country_id int, weather_state int, day date);
Truncate table 70_Countries;
insert into 70_Countries (country_id, country_name) values ('2', 'USA');
insert into 70_Countries (country_id, country_name) values ('3', 'Australia');
insert into 70_Countries (country_id, country_name) values ('7', 'Peru');
insert into 70_Countries (country_id, country_name) values ('5', 'China');
insert into 70_Countries (country_id, country_name) values ('8', 'Morocco');
insert into 70_Countries (country_id, country_name) values ('9', 'Spain');
Truncate table 70_Weather;
insert into 70_Weather (country_id, weather_state, day) values ('2', '15', '2019-11-01');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-28');
insert into 70_Weather (country_id, weather_state, day) values ('2', '12', '2019-10-27');
insert into 70_Weather (country_id, weather_state, day) values ('3', '-2', '2019-11-10');
insert into 70_Weather (country_id, weather_state, day) values ('3', '0', '2019-11-11');
insert into 70_Weather (country_id, weather_state, day) values ('3', '3', '2019-11-12');
insert into 70_Weather (country_id, weather_state, day) values ('5', '16', '2019-11-07');
insert into 70_Weather (country_id, weather_state, day) values ('5', '18', '2019-11-09');
insert into 70_Weather (country_id, weather_state, day) values ('5', '21', '2019-11-23');
insert into 70_Weather (country_id, weather_state, day) values ('7', '25', '2019-11-28');
insert into 70_Weather (country_id, weather_state, day) values ('7', '22', '2019-12-01');
insert into 70_Weather (country_id, weather_state, day) values ('8', '25', '2019-11-05');
insert into 70_Weather (country_id, weather_state, day) values ('8', '27', '2019-11-15');
insert into 70_Weather (country_id, weather_state, day) values ('8', '31', '2019-11-25');
insert into 70_Weather (country_id, weather_state, day) values ('9', '7', '2019-10-23');
insert into 70_Weather (country_id, weather_state, day) values ('9', '3', '2019-12-23');

最终sql：

select 
    country_name,
    (case 
         when avg(weather_state)<=15 then 'Cold'
         when avg(weather_state)>=25 then 'Hot'
         else 'Warm'
     end ) weather_type
from 
    70_Countries c 
left join 
    70_Weather w
on
    c.country_id = w.country_id
where
    date_format(day,"%Y-%m")='2019-11'
group by country_name;

71. 求团队人数

编写一个 SQL 查询，以求得每个员工所在团队的总人数。查询结果中的顺序无特定要求。

展示效果：

+-------------+------------+
| employee_id | team_size  |
+-------------+------------+
|     1       |     3      |
|     2       |     3      |
|     3       |     3      |
|     4       |     1      |
|     5       |     2      |
|     6       |     2      |
+-------------+------------+

建表语句：

Create table If Not Exists 71_Employee (employee_id int, team_id int);
Truncate table 71_Employee;
insert into 71_Employee (employee_id, team_id) values ('1', '8');
insert into 71_Employee (employee_id, team_id) values ('2', '8');
insert into 71_Employee (employee_id, team_id) values ('3', '8');
insert into 71_Employee (employee_id, team_id) values ('4', '7');
insert into 71_Employee (employee_id, team_id) values ('5', '9');
insert into 71_Employee (employee_id, team_id) values ('6', '9');

最终sql：

SELECT employee_id, (
    SELECT COUNT(*)
    FROM 71_employee e2
    WHERE e1.team_id = e2.team_id
) AS team_size
FROM 71_Employee e1
ORDER BY e1.employee_id;

SELECT e1.employee_id, COUNT(*) AS team_size
FROM 71_Employee e1 JOIN 71_Employee e2 USING (team_id)
GROUP BY e1.employee_id
ORDER BY e1.employee_id;

SELECT
    employee_id,
    COUNT(employee_id) OVER(PARTITION BY team_id) AS team_size
FROM 71_Employee
ORDER BY employee_id;

72. 不同性别每日分数总计

写一条SQL语句查询每种性别在每一天的总分，并按性别和日期对查询结果排序

展示效果：

+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+

建表语句：

Create table If Not Exists 72_Scores (player_name varchar(20), gender varchar(1), day date, score_points int);
Truncate table 72_Scores;
insert into 72_Scores (player_name, gender, day, score_points) values ('Aron', 'F', '2020-01-01', '17');
insert into 72_Scores (player_name, gender, day, score_points) values ('Alice', 'F', '2020-01-07', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Bajrang', 'M', '2020-01-07', '7');
insert into 72_Scores (player_name, gender, day, score_points) values ('Khali', 'M', '2019-12-25', '11');
insert into 72_Scores (player_name, gender, day, score_points) values ('Slaman', 'M', '2019-12-30', '13');
insert into 72_Scores (player_name, gender, day, score_points) values ('Joe', 'M', '2019-12-31', '3');
insert into 72_Scores (player_name, gender, day, score_points) values ('Jose', 'M', '2019-12-18', '2');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priya', 'F', '2019-12-31', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priyanka', 'F', '2019-12-30', '17');

最终SQL：

-- 方法一
SELECT 
     s1.gender,
     s1.day,
     SUM(s2.score_points) AS total
FROM 
     Scores AS s1 
JOIN 
     Scores AS s2
ON 
     s1.gender = s2.gender 
     AND
     s1.day >= s2.day
GROUP BY 
     s1.gender, s1.day
ORDER BY 
     s1.gender, s1.day;

-- 方法二
SELECT 
     gender,
     day,
     SUM(score_points) OVER (PARTITION BY gender ORDER BY day) AS total
FROM Scores;

73. 餐馆营业额变化增长

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值

查询结果格式的例子如下：

  • 查询结果按 visited_on 排序
  • average_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

展示效果：

+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120.00         |
| 2019-01-09   | 840          | 120.00         |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+

Create table If Not Exists 73_Customer (customer_id int, name varchar(20), visited_on date, amount int);
Truncate table 73_Customer;
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');

最终sql：

-- 方法一
select
    s.visited_on,
    sum(c.amount) as "amount",
    round(sum(c.amount) / 7, 2) as "average_amount"
from 
    73_Customer c 
inner join 
   (select
         distinct visited_on
    from
         73_Customer c 
    where 
         visited_on >= (select
                              distinct visited_on
                        from
                              73_customer 
                        order by
                              visited_on asc
                        limit 1 offset 6)
   ) s 
on 
   datediff(s.visited_on, c.visited_on) >= 0 
   and
   datediff(s.visited_on, c.visited_on) < 7
group by
   s.visited_on;

-- 方法二
select  a.visited_on, round(sum(b.amount)/cf,0) as amount, round(sum(b.amount)/(7*cf),2) as average_amount
from Customer a,Customer b,
(select visited_on,count(1) as cf
from Customer
group by visited_on) c
where datediff(a.visited_on,b.visited_on)>=0 and datediff(a.visited_on,b.visited_on)<7  and c.visited_on=a.visited_on
group by a.visited_on
having DATE_ADD(a.visited_on,INTERVAL -6 DAY)in (select visited_on from Customer)


select  visited_on,amount,round(amount/7,2) average_amount
from (
    select visited_on,ant,lag(visited_on,6,null) over(order by visited_on) lg,
            sum(ant) over(order by visited_on rows between 6 PRECEDING and current row) amount
    from(
        select  visited_on  ,sum(amount) ant
        from Customer
        group by visited_on
    )t1
)t2
where lg is not null

select to_char(visited_on,'yyyy-MM-dd') visited_on, amount, round(amount / 7, 2) average_amount
from (select visited_on,
sum(amount) over(order by visited_on rows between 6 preceding and current row) amount
from (select visited_on, sum(amount) amount
from Customer
group by visited_on))
where visited_on in
(select distinct visited_on
from Customer
where to_number(to_char(visited_on, 'yyyyMMdd')) - 6 >=
(select to_number(to_char(min(visited_on), 'yyyyMMdd')) from Customer)
)

74. 广告效果

写一条SQL语句来查询每一条广告的 ctr ， ctr 要保留两位小数。结果需要按 ctr 降序、按 ad_id 升序 进行排序。

广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:

img

展示效果：

+-------+-------+
| ad_id | ctr   |
+-------+-------+
| 1     | 66.67 |
| 3     | 50.00 |
| 2     | 33.33 |
| 5     | 0.00  |
+-------+-------+

Create table If Not Exists 74_Ads (ad_id int, user_id int, action ENUM('Clicked', 'Viewed', 'Ignored'));
Truncate table 74_Ads;
insert into 74_Ads (ad_id, user_id, action) values ('1', '1', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('2', '2', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('3', '3', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('5', '5', 'Ignored');
insert into 74_Ads (ad_id, user_id, action) values ('1', '7', 'Ignored');
insert into 74_Ads (ad_id, user_id, action) values ('2', '7', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('3', '5', 'Clicked');
insert into 74_Ads (ad_id, user_id, action) values ('1', '4', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('2', '11', 'Viewed');
insert into 74_Ads (ad_id, user_id, action) values ('1', '2', 'Clicked');

最终SQL：

-- 方法一
SELECT
     ad_id,
     ROUND(IFNULL(SUM(action = 'Clicked')
                    /
             (SUM(action = 'Clicked') + SUM(action = 'Viewed')) * 100, 0), 2) AS ctr
FROM 
    74_Ads
GROUP BY
    ad_id
ORDER BY
    ctr DESC,
    ad_id ASC;

-- 方法二
select 
     ad_id,
     ifnull(round((Clicked/(Viewed + Clicked))*100,2),0) as ctr
from
    (select
           ad_id,
           count(case when action = 'Clicked' then 1 end) as Clicked,
           count(case when action = 'Viewed' then 1 end) as Viewed
     from 74_Ads 
     group by ad_id ) a 
order by 
     ctr desc,
     ad_id asc;

-- 方法三
select 
     a.ad_id,
     ifnull(ctr,0) ctr
from 
     74_Ads a
left join 
     (select
           ad_id,
           round(sum(if(action='Clicked',1,0))/count(*)*100,2) ctr 
      from 74_Ads 
      where action !='Ignored'
      group by ad_id )t1 
on 
      a.ad_id= t1.ad_id
group by 
      ad_id,ctr
order by
      ctr desc,ad_id;

75. 列出指定时间段内所有的下单产品

写一个 SQL 语句，要求获取在 2020 年 2 月份下单的数量不少于 100 的产品的名字和数目。返回结果表单的顺序无要求。

展示效果：

+--------------------+---------+
| product_name       | unit    |
+--------------------+---------+
| Leetcode Solutions | 130     |
| Leetcode Kit       | 100     |
+--------------------+---------+

Create table If Not Exists 75_Products (product_id int, product_name varchar(40), product_category varchar(40));
Create table If Not Exists 75_Orders (product_id int, order_date date, unit int);
Truncate table 75_Products;
insert into 75_Products (product_id, product_name, product_category) values ('1', 'Leetcode Solutions', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('2', 'Jewels of Stringology', 'Book');
insert into 75_Products (product_id, product_name, product_category) values ('3', 'HP', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('4', 'Lenovo', 'Laptop');
insert into 75_Products (product_id, product_name, product_category) values ('5', 'Leetcode Kit', 'T-shirt');
Truncate table 75_Orders;
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-05', '60');
insert into 75_Orders (product_id, order_date, unit) values ('1', '2020-02-10', '70');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-01-18', '30');
insert into 75_Orders (product_id, order_date, unit) values ('2', '2020-02-11', '80');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-17', '2');
insert into 75_Orders (product_id, order_date, unit) values ('3', '2020-02-24', '3');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-01', '20');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '30');
insert into 75_Orders (product_id, order_date, unit) values ('4', '2020-03-04', '60');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-25', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-02-27', '50');
insert into 75_Orders (product_id, order_date, unit) values ('5', '2020-03-01', '50');

-- 方法一
select 
    p.product_name,
    sum(o.unit) as unit
from
    75_Products p
left join
    75_Orders o 
on
    p.product_id=o.product_id
where
    order_date between '2020-02-01' and '2020-02-29'
group by
    p.product_name
having
    sum(o.unit)>=100
order by
    sum(o.unit);
    
-- 方法二 
SELECT 
     product_name, 
     SUM(unit) AS unit
FROM 
     75_Products 
JOIN 
     75_Orders USING (product_id)
WHERE 
     order_date LIKE "2020-02%"
GROUP BY
     product_name
HAVING
     unit >= 100;

-- 方法三
select 
     T.product_name,
     T.unit
from
    (select
          p.product_name,
          sum(unit) as unit
     from
          75_Orders o
     join
          75_Products p
     on 
          o.product_id = p.product_id
     where
         order_date like "2020-02%"
    group by
         p.product_id
    ) as T
where
    T.unit>= 100;

76. 每次访问的交易次数

写一条 SQL 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：

  • transactions_count： 客户在一次访问中的交易次数
  • visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量

展示效果：

+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0                  | 4            |
| 1                  | 5            |
| 2                  | 0            |
| 3                  | 1            |
+--------------------+--------------+

提示：

  • 对于 transactions_count = 0, visits 中 (1, “2020-01-01”), (2, “2020-01-02”), (12, “2020-01-01”) 和 (19, “2020-01-03”) 没有进行交易，所以 visits_count = 4 。
  • 对于 transactions_count = 1, visits 中 (2, “2020-01-03”), (7, “2020-01-11”), (8, “2020-01-28”), (1, “2020-01-02”) 和 (1, “2020-01-04”) 进行了一次交易，所以 visits_count = 5 。
  • 对于 transactions_count = 2, 没有客户访问银行进行了两次交易，所以 visits_count = 0 。
  • 对于 transactions_count = 3, visits 中 (9, “2020-01-25”) 进行了三次交易，所以 visits_count = 1 。
  • 对于 transactions_count >= 4, 没有客户访问银行进行了超过3次交易，所以我们停止在 transactions_count = 3 。
Create table If Not Exists 76_Visits (user_id int, visit_date date);
Create table If Not Exists 76_Transactions (user_id int, transaction_date date, amount int);
Truncate table 76_Visits;
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-01');
insert into 76_Visits (user_id, visit_date) values ('2', '2020-01-02');
insert into 76_Visits (user_id, visit_date) values ('12', '2020-01-01');
insert into 76_Visits (user_id, visit_date) values ('19', '2020-01-03');
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-02');
insert into 76_Visits (user_id, visit_date) values ('2', '2020-01-03');
insert into 76_Visits (user_id, visit_date) values ('1', '2020-01-04');
insert into 76_Visits (user_id, visit_date) values ('7', '2020-01-11');
insert into 76_Visits (user_id, visit_date) values ('9', '2020-01-25');
insert into 76_Visits (user_id, visit_date) values ('8', '2020-01-28');
Truncate table 76_Transactions;
insert into 76_Transactions (user_id, transaction_date, amount) values ('1', '2020-01-02', '120');
insert into 76_Transactions (user_id, transaction_date, amount) values ('2', '2020-01-03', '22');
insert into 76_Transactions (user_id, transaction_date, amount) values ('7', '2020-01-11', '232');
insert into 76_Transactions (user_id, transaction_date, amount) values ('1', '2020-01-04', '7');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '33');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '66');
insert into 76_Transactions (user_id, transaction_date, amount) values ('8', '2020-01-28', '1');
insert into 76_Transactions (user_id, transaction_date, amount) values ('9', '2020-01-25', '99');

最终SQL：

-- 方法一
SELECT
     *
FROM
     (SELECT
            t5.rnb AS transactions_count,
            IFNULL(visits_count, 0) AS visits_count
      FROM
           (SELECT
                 0 AS rnb
            UNION
            SELECT
                 ROW_NUMBER() OVER () AS rnb
            FROM
                 76_Transactions
            ) t5
      LEFT JOIN
           (SELECT
                 cnt AS transactions_count,
                 COUNT(user_id) AS visits_count
            FROM
                (SELECT
                      t1.user_id, 
                      COUNT(t2.amount) AS cnt
                 FROM
                      76_Visits t1
                 LEFT JOIN
                      76_Transactions t2
                 ON 
                      t1.user_id = t2.user_id
                      AND
                      t1.visit_date = t2.transaction_date
                 GROUP BY
                      user_id, 
                      visit_date ) t3
             GROUP BY cnt ) t4
       ON t5.rnb = t4.transactions_count) t6
WHERE transactions_count <= (SELECT
                                   COUNT(t2.amount) AS cnt
                              FROM 
                                   76_Visits t1
                              LEFT JOIN
                                   76_Transactions t2
                              ON
                                   t1.user_id = t2.user_id
                                   AND
                                   t1.visit_date = t2.transaction_date
                              GROUP BY
                                   t1.user_id, visit_date
                              ORDER BY
                                   cnt DESC
                              LIMIT 1);

-- 方法二
select 
     pcnt transactions_count,
     count(*) visits_count
from 
    (select
          visit_date,
          sum(if(amount is  null,0,1)) over(partition by transaction_date ) pcnt,
          count(*) over(partition by  visit_date ) tcnt
     from 
          76_Visits v 
     left join
          76_Transactions t
     on
          v.user_id= t.user_id
          and
          v.visit_date=t.transaction_date
     )t1
group by pcnt;

77. 电影评分

请你编写一组 SQL 查询：查找评论电影数量最多的用户名，如果出现平局，返回字典序较小的用户名。查找在2020 年 2 月 平均评分最高的电影名称，如果出现平局，返回字典序较小的电影名称。

展示效果：

+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+

建表语句：

Create table If Not Exists 77_Movies (movie_id int, title varchar(30));
Create table If Not Exists 77_Users (user_id int, name varchar(30));
Create table If Not Exists 77_Movie_Rating (movie_id int, user_id int, rating int, created_at date);
Truncate table 77_Movies;
insert into 77_Movies (movie_id, title) values ('1', 'Avengers');
insert into 77_Movies (movie_id, title) values ('2', 'Frozen 2');
insert into 77_Movies (movie_id, title) values ('3', 'Joker');
Truncate table 77_Users;
insert into 77_Users (user_id, name) values ('1', 'Daniel');
insert into 77_Users (user_id, name) values ('2', 'Monica');
insert into 77_Users (user_id, name) values ('3', 'Maria');
insert into 77_Users (user_id, name) values ('4', 'James');
Truncate table 77_Movie_Rating;
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '1', '3', '2020-01-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '2', '4', '2020-02-11');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '3', '2', '2020-02-12');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('1', '4', '1', '2020-01-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '1', '5', '2020-02-17');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '2', '2', '2020-02-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('2', '3', '2', '2020-03-01');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '1', '3', '2020-02-22');
insert into 77_Movie_Rating (movie_id, user_id, rating, created_at) values ('3', '2', '4', '2020-02-25');

最终SQL：

select name results 
from
    (select
          m.user_id,
          u.name 
    from
          77_Movie_Rating m 
    left join
          77_Users u 
    on
          m.user_id = u.user_id
    group by
          user_id
    order by
          count(*) desc,name
    limit 1)t1
    
    union 
    (select
          title results
     from 
          77_Movie_Rating r 
     left join
          77_Movies m
     on
          r.movie_id =m.movie_id 
     where
         date_format(created_at,'%Y-%m')='2020-02'
     group by 
         r.movie_id
     order by 
         avg(rating) desc,title 
     limit 1);

78. 院系无效的学生

写一条 SQL 语句以查询那些所在院系不存在的学生的 id 和姓名，可以以任何顺序返回结果

展示效果：


+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+

建表语句：

Create table If Not Exists 78_Departments (id int, name varchar(30));
Create table If Not Exists 78_Students (id int, name varchar(30), department_id int);
Truncate table 78_Departments;
insert into 78_Departments (id, name) values ('1', 'Electrical Engineering');
insert into 78_Departments (id, name) values ('7', 'Computer Engineering');
insert into 78_Departments (id, name) values ('13', 'Bussiness Administration');
Truncate table 78_Students;
insert into 78_Students (id, name, department_id) values ('23', 'Alice', '1');
insert into 78_Students (id, name, department_id) values ('1', 'Bob', '7');
insert into 78_Students (id, name, department_id) values ('5', 'Jennifer', '13');
insert into 78_Students (id, name, department_id) values ('2', 'John', '14');
insert into 78_Students (id, name, department_id) values ('4', 'Jasmine', '77');
insert into 78_Students (id, name, department_id) values ('3', 'Steve', '74');
insert into 78_Students (id, name, department_id) values ('6', 'Luis', '1');
insert into 78_Students (id, name, department_id) values ('8', 'Jonathan', '7');
insert into 78_Students (id, name, department_id) values ('7', 'Daiana', '33');
insert into 78_Students (id, name, department_id) values ('11', 'Madelynn', '1');

最终SQL：

-- 方法一
select
     id,
     name  
from 
     78_Students 
where
     department_id not in (select id from 78_Departments)
order by
     id asc;

-- 方法二
select 
     s.id,
     s.name
from 
     78_students s 
left join 
     78_Departments d
on 
     s.department_id=d.id
where
     d.id is null;
龍浮影
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
超全SQL题（104道包含MySQL新特性解法等）由浅入深、笔试必备！（第五部分53-65）
qq_48784015的博客
09-05 318
53. 文章浏览 需求一：找出所有浏览过自己文章的作者，结果按照 id 升序排列。 展示效果： id 4 7 Create table If Not Exists 53_Views (article_id int, author_id int, viewer_id int, view_date date); Truncate table 53_Views; insert into 53_Views (article_id, author_id, viewer_id, view_dat
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第七部分79-91）
qq_48784015的博客
09-06 368
79. 活动参与者 写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字，可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表 展示效果： +--------------+ | activity | +--------------+ | Singing | +--------------+ Create table If Not Exists 79_Friends (id int, name varchar(30), activity
leetcode-SQL-1280. 学生们参加各科测试的次数
菊头蝙蝠的博客
04-28 334
leetcode-SQL-1280. 学生们参加各科测试的次数题目解题 题目 题目链接 学生表: Students +---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ 主键为 student_id（学生ID），该表内的每一行都记
写sql语句统计各个学生各科成绩（case when用法）
热门推荐
格局、视野、认知
10-17 2万+
有如下一张表score: 建表语句： CREATE TABLE `score` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(20) NOT NULL ,`category` varchar(20) NOT NULL ,`score` int NOT NULL );         现在要去查询学生的各科成绩和总分，...
2021-06-01
小蚂蚁的博客
06-01 253
学生们参加各科测试的次数 需求：写一段 SQL 语句，查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。 展示效果： +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+-
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第一部分1-13）
qq_48784015的博客
09-05 175
1. 组合两个表 需求：编写一个 SQL 查询，对两表进行关联，展示列为： FirstName, LastName, City, State 展示效果： FirstName LastName City State Allen Wang New York City New York Create table If Not Exists 1_Person (PersonId int, FirstName varchar(255), LastName varchar(255)); Create
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第四部分40-52）
qq_48784015的博客
09-05 204
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
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第八部分92-104）
qq_48784015的博客
09-06 677
92. 活跃用户 写一个 SQL 查询, 找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户。返回的结果表按照 id 排序. 展示数据： +----+----------+ | id | name | +----+----------+ | 7 | Jonathan | +----+----------+ id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户. id = 7 的用户 Jonathon 在
超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第三部分27-39）
qq_48784015的博客
09-05 214
27. 体育馆人流量 需求：请编写一个查询语句，找出人流量的高峰期。高峰期定义，至少连续三行记录中的人流量不少于100。 展示效果： id visit_date people 5 2017-01-05 145 6 2017-01-06 1455 7 2017-01-07 199 8 2017-01-08 188 Create table If Not Exists 27_stadium (id int, visit_date DATE NULL, people int); T
2023最新MySQL100道面试题-附答案解析
03-30
MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源...
新手必备!mysql 8.0.15 下载安装详细教程 -供大家学习研究参考
12-16
mysql下载安装教程 安装服务 接下来安装服务，使用mysqld --install [服务名]，这里服务可以不写，默认为mysql，如果电脑上安装有多个服务，这里就可以进行不同的命名进行区分。 如果服务已经安装，会提示the ...
MySQL面试题(含答案)- MySQL面试题及答案
02-20
MySQL面试题(含答案)| MySQL面试题及答案
mysql最新笔试题以及答案
04-19
这是统计的最新的mysql笔试题，总共有四十几个问题，答案也在对应的问题下边，只要用心看完，相信你会有所提升。
MySQL面试题经典40问！(全）（含答案解释）
03-05
MySQL面试题经典40问！(全） 1、什么是数据库事务？ 2、 Mysql 事务的四大特性是什么？ 3、事务 ACID 特性的实现原理？ 4、事务的隔离级别有哪些？ 5、什么是脏读、不可重复读、幻读呢？ 6、 datetime 和 timestamp ...
在JPA项目启动时新增MySQL字段
VioletKiss
06-18 981
我用的方案1，如果你也是这种项目，那么我也推荐你用方案1，简单粗暴又方便；方案2是问了chatgpt得出来的答案，感觉不错，但是报错了又不想改；不建议使用 schema.sql 和 data.sql 对数据库结构及数据进行初始化，因为在每一次项目启动，这两个文件都必须执行一次，大大减慢了项目的启动速度，替代方案可以是使用 flyway 进行数据库版本控制。
mysqladmin——MySQL Server管理程序（二）
u011565038的博客
06-22 897
mysqladmin是一个命令行工具，用于执行简单的 MySQL 服务器管理任务，如检查服务器的状态、创建和删除数据库、重载权限等。
MySQL-DML-约束
weixin_51788042的博客
06-18 646
创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。以上的删除属于DML的方式删除，这种删除的数据是可以通过事务回滚的方式重新恢复的，但是删除的效率较低。另外还有一种删除表中数据的方式，但是这种方式不支持事务，不可以回滚，删了之后数据是永远也找不回来了。email字段设置为唯一性，唯一性的字段值是可以为NULL的。表名后面的小括号当中的字段名如果省略掉，表示自动将所有字段都列出来了，并且字段的顺序和建表时的顺序一致。注意：这个语句删除效率非常高，巨大的表，瞬间干掉所有数据。
Mysql之GROUP BY与PARTITION BY区别
最新发布
m0_51014049的博客
06-22 264
PARTITION BY 是窗口函数的一部分，用于定义窗口（即数据分区），但是它不会改变结果集的行数。PARTITION BY 用于窗口函数内，将结果集划分为几个分区，计算聚合值或其他窗口函数。这个查询会返回每一行，并在每一行中增加一个列 totalAmount，它表示的是该 employee_id 内所有 amount 的总和。结果集：GROUP BY 改变结果集的行数，只返回每组一行。PARTITION BY 用于窗口函数，增加计算列，不改变结果集的结构。
mysql数据库笔试试题及答案(全)
04-30
下面就是关于MySQL数据库笔试试题及答案（全）的详细介绍。 1. MySQL中的MYISAM和InnoDB引擎有什么区别？ 答：MYISAM和InnoDB都是MySQL常用的引擎，但是它们有一些重要的区别。MYISAM引擎读写速度快，但是不支持...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值