超全SQL题（104道包含MySQL新特性解法等）由浅入深、笔试必备！（第五部分53-65）

于 2020-09-05 16:36:40 发布
阅读量318 收藏 1
点赞数
文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_48784015/article/details/108421414
版权

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_date) values (1, 3, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 5, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (1, 3, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 7, '2019-08-01');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (2, 7, 6, '2019-08-02');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (4, 7, 1, '2019-07-22');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');
insert into 53_Views (article_id, author_id, viewer_id, view_date) values (3, 4, 4, '2019-07-21');

最终SQL:

select 
      distinct viewer_id as id
from 
      53_Views 
where 
      viewer_id = author_id
order by 
      viewer_id;

需求二：找出在同一天阅读至少两篇文章的人，结果按照 id 升序排序。

展示效果：

project_id
5
6

最终SQL:

SELECT 
      DISTINCT viewer_id as id 
FROM 
      53_views
GROUP BY 
      viewer_id,view_date
HAVING
      COUNT(DISTINCT article_id)>=2
ORDER BY 
      viewer_id;

54. Market Analysis

需求一：Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

展示效果：

buyer_idjoin_dateorders_in_2019
12018-01-011
22018-02-092
32018-01-190
42018-05-210
Create table If Not Exists 54_Users (user_id int, join_date date, favorite_brand varchar(10));
create table if not exists 54_Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int);
create table if not exists 54_Items (item_id int, item_brand varchar(10));
Truncate table 54_Users;
insert into 54_Users (user_id, join_date, favorite_brand) values (1, '2018-01-01', 'Lenovo');
insert into 54_Users (user_id, join_date, favorite_brand) values (2, '2018-02-09', 'Samsung');
insert into 54_Users (user_id, join_date, favorite_brand) values (3, '2018-01-19', 'LG');
insert into 54_Users (user_id, join_date, favorite_brand) values (4, '2018-05-21', 'HP');
Truncate table 54_Orders;
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (1, '2019-08-01', 4, 1, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (2, '2018-08-02', 2, 1, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (3, '2019-08-03', 3, 2, 3);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (4, '2018-08-04', 1, 4, 2);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (5, '2018-08-04', 1, 3, 4);
insert into 54_Orders (order_id, order_date, item_id, buyer_id, seller_id) values (6, '2019-08-05', 2, 2, 4);
Truncate table 54_Items;
insert into 54_Items (item_id, item_brand) values (1, 'Samsung');
insert into 54_Items (item_id, item_brand) values (2, 'Lenovo');
insert into 54_Items (item_id, item_brand) values (3, 'LG');
insert into 54_Items (item_id, item_brand) values (4, 'HP');

最终SQL:

SELECT 
       user_id AS buyer_id,
       join_date,
       IFNULL(COUNT(buyer_Id), 0) AS orders_in_2019
FROM 
       54_Users u 
LEFT JOIN
       54_Orders o
ON  
       U.user_id = o.buyer_id AND
       order_date >= '2019-01-01'
GROUP BY
       user_id
ORDER BY
       user_id;

需求二：Write an SQL query to find for each user, whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as no.

展示效果：

product_id2nd_item_fav_brand
1no
2no
3yes
4no

最终SQL:

select 
      user_id as seller_id,
      case
         when t3.item_brand is null then 'no'
         when t3.item_brand = u.favorite_brand then 'yes'
         else 'no' 
      end as 2nd_item_fav_brand
from 
      54_Users u
left join 
     (select
            order_id,
            order_date,
            t2.item_id,
            buyer_id,
            seller_id,
            i.item_brand as item_brand
      from 
           (select
                  order_id,
                  order_date,
                  item_id,
                  buyer_id,
                  seller_id,
                  cast(if(@prev = seller_id,@rank := @rank + 1,@rank := 1) as unsigned) as rank,
                  @prev := seller_id as prev
            from 
                 (select
                         order_id,
                         order_date,
                         item_id,
                         buyer_id,
                         seller_id
                  from
                         54_Orders
                  group by
                         seller_id,
                         order_date
                 ) as t1,
                 (select 
                         @rank := 0,
                         @prev := null
                 ) as init) t2,
                 54_Items i
            where
                  rank = 2 and
                  t2.item_id = i.item_id
     ) as t3
on 
       u.user_id = t3.seller_id;

55. Product Price at a Given Date

需求一：Write an SQL query to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

展示效果：

project_idprice
250
135
310
Create table If Not Exists 55_Products (product_id int, new_price int, change_date date);
Truncate table 55_Products;
insert into 55_Products (product_id, new_price, change_date) values (1, 20, '2019-08-14');
insert into 55_Products (product_id, new_price, change_date) values (2, 50, '2019-08-14');
insert into 55_Products (product_id, new_price, change_date) values (1, 30, '2019-08-15');
insert into 55_Products (product_id, new_price, change_date) values (1, 35, '2019-08-16');
insert into 55_Products (product_id, new_price, change_date) values (2, 65, '2019-08-17');
insert into 55_Products (product_id, new_price, change_date) values (3, 20, '2019-08-18');

最终SQL:

SELECT
    * 
FROM 
    (SELECT 
           product_id,
           new_price AS price
     FROM 
           55_Products
     WHERE (product_id, change_date) IN (
                                          SELECT
                                                product_id,
                                                MAX(change_date)
                                          FROM 
                                                55_Products
                                          WHERE 
                                                change_date <= '2019-08-16'
                                          GROUP BY
                                                product_id
                                         )
     UNION
     SELECT
            DISTINCT product_id, 10 AS price
     FROM 
            55_Products
     WHERE 
            product_id NOT IN (SELECT
                                     product_id
                               FROM  
                                     55_Products
                               WHERE change_date <= '2019-08-16'
                              )
     ) tmp
ORDER BY 
     price DESC;

56. Immediate Food Delivery

需求一：查询语句获取即时订单所占的百分比， 保留两位小数。

展示效果：

immediate_percentage
42.86
Create table If Not Exists 56_Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table 56_Delivery;
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (1, 1, '2019-08-01', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (2, 5, '2019-08-02', '2019-08-02');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (3, 1, '2019-08-11', '2019-08-11');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (4, 3, '2019-08-24', '2019-08-26');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (5, 4, '2019-08-21', '2019-08-22');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (6, 2, '2019-08-11', '2019-08-13');
insert into 56_Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values (7, 4, '2019-08-09', '2019-08-09');

最终SQL:

SELECT ROUND(
    (SELECT
           COUNT(delivery_id)
    FROM 
           56_Delivery
    WHERE 
           order_date = customer_pref_delivery_date
    ) * 100 / COUNT(delivery_id) , 2) AS immediate_percentage
FROM 
    56_Delivery;

需求二：查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

展示效果：

immediate_percentage
40.00

最终SQL:

select
      round(
               count(case when d.order_date = d.customer_pref_delivery_date then 1 end)
               * 
               100/count(*), 2) as immediate_percentage
from 
     56_Delivery d,
    (select
           delivery_id,
           customer_id,
           min(order_date) as order_date
     from
           56_Delivery
     group by
           customer_id
    ) as t
where
     d.customer_id = t.customer_id
     and d.order_date = t.order_date;

57. 重新格式化部门表

需求一：编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

展示效果：

idJan_RevenueFeb_RevenueMar_Revenue…Dec_Revenue
1800070006000…null
29000nullnull…null
3null10000null…null
Create table If Not Exists 57_Department (id int, revenue int, month varchar(5));
Truncate table 57_Department;
insert into 57_Department (id, revenue, month) values (1, 8000, 'Jan');
insert into 57_Department (id, revenue, month) values (2, 9000, 'Jan');
insert into 57_Department (id, revenue, month) values (3, 10000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 7000, 'Feb');
insert into 57_Department (id, revenue, month) values (1, 6000, 'Mar');

最终SQL:

SELECT 
      DISTINCT id AS "id",
      SUM(IF (month = "Jan", revenue, null)) AS "Jan_Revenue",
      SUM(IF (month = "Feb", revenue, null)) AS "Feb_Revenue",
      SUM(IF (month = "Mar", revenue, null)) AS "Mar_Revenue",
      SUM(IF (month = "Apr", revenue, null)) AS "Apr_Revenue",
      SUM(IF (month = "May", revenue, null)) AS "May_Revenue",
      SUM(IF (month = "Jun", revenue, null)) AS "Jun_Revenue",
      SUM(IF (month = "Jul", revenue, null)) AS "Jul_Revenue",
      SUM(IF (month = "Aug", revenue, null)) AS "Aug_Revenue",
      SUM(IF (month = "Sep", revenue, null)) AS "Sep_Revenue",
      SUM(IF (month = "Oct", revenue, null)) AS "Oct_Revenue",
      SUM(IF (month = "Nov", revenue, null)) AS "Nov_Revenue",
      SUM(IF (month = "Dec", revenue, null)) AS "Dec_Revenue" 
FROM 
      57_Department
GROUP BY id;

58. 每月交易

需求一：查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

展示效果：

monthcountrytrans_countapproved_counttrans_total_amountapproved_total_amount
2018-12US2130001000
2019-01US1120002000
2019-01DE1120002000
2019-05US2130001000
2019-06US32120008000
create table if not exists 58_Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date);
create table if not exists Chargebacks (trans_id int, trans_date date);
truncate table 58_Transactions;
insert into 58_Transactions (id, country, state, amount, trans_date) values (101, 'US', 'approved', 1000, '2018-12-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (102, 'US', 'declined', 2000, '2018-12-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (103, 'US', 'approved', 2000, '2019-01-01');
insert into 58_Transactions (id, country, state, amount, trans_date) values (104, 'DE', 'approved', 2000, '2019-01-07');
insert into 58_Transactions (id, country, state, amount, trans_date) values (105, 'US', 'approved', 1000, '2019-05-18');
insert into 58_Transactions (id, country, state, amount, trans_date) values (106, 'US', 'declined', 2000, '2019-05-19');
insert into 58_Transactions (id, country, state, amount, trans_date) values (107, 'US', 'approved', 3000, '2019-06-10');
insert into 58_Transactions (id, country, state, amount, trans_date) values (108, 'US', 'declined', 4000, '2019-06-13');
insert into 58_Transactions (id, country, state, amount, trans_date) values (109, 'US', 'approved', 5000, '2019-06-15');
truncate table 58_Chargebacks;
insert into 58_Chargebacks (trans_id, trans_date) values (102, '2019-05-29');
insert into 58_Chargebacks (trans_id, trans_date) values (101, '2019-06-30');
insert into 58_Chargebacks (trans_id, trans_date) values (105, '2019-09-18');

最终SQL:

select
      date_format(trans_date,'%Y-%m') as month,
      country,
      count(*) as trans_count,
      sum(if(state='approved',1,0)) as approved_count,
      sum(amount) as trans_total_amount,
      sum(if(state='approved',amount,0)) as approved_total_amount
from 
      58_Transactions t
group by
      date_format(trans_date,'%Y-%m'),
      country;

需求二：编写一个 SQL 查询，以查找每个月和每个国家/地区的已批准交易的数量及其总金额、退单的数量及其总金额。

展示效果：

monthcountryapproved_countapproved_amountchargeback_countchargeback_amount
2018-12US1100000
2019-01DE1200000
2019-01US1200000
2019-05US1100012000
2019-06US2800011000
2019-09US0011000

最终SQL:

SELECT
       month as MONTH,
       country as COUNTRY,
       SUM(IF(type = 'approved', 1, 0)) AS APPROVED_COUNT,
       SUM(IF(type = 'approved', amount, 0)) AS APPROVED_AMOUNT,
       SUM(IF(type = 'chargeback', 1, 0)) AS CHARGEBACK_COUNT,
       SUM(IF(type = 'chargeback', amount, 0)) AS CHARGEBACK_AMOUNT
FROM 
      (SELECT 
              date_format(t.trans_date,'%Y-%m') AS month,
              t.country,
              amount,
              'approved' AS type
        FROM
              58_Transactions AS t
        WHERE 
              state = 'approved'
        UNION ALL
        SELECT
              date_format(c.trans_date,'%Y-%m') AS month,
              t.country,
              amount,
              'chargeback' AS type
         FROM 
              58_Transactions AS t
         INNER JOIN
              58_Chargebacks AS c 
         ON t.id = c.trans_id
         ) AS tt
GROUP BY 
         tt.month,
         tt.country;

59. 锦标赛优胜者

需求一：编写一个 SQL 查询来查找每组中的获胜者。每组的获胜者是在组内得分最高的选手。如果平局，得分最低的选手获胜。

展示效果：

group_idplayer_id
115
235
340
Create table If Not Exists 59_Players (player_id int, group_id int);
Create table If Not Exists 59_Matches (match_id int, first_player int, second_player int, first_score int, second_score int);
Truncate table 59_Players;
insert into 59_Players (player_id, group_id) values (10, 2);
insert into 59_Players (player_id, group_id) values (15, 1);
insert into 59_Players (player_id, group_id) values (20, 3);
insert into 59_Players (player_id, group_id) values (25, 1);
insert into 59_Players (player_id, group_id) values (30, 1);
insert into 59_Players (player_id, group_id) values (35, 2);
insert into 59_Players (player_id, group_id) values (40, 3);
insert into 59_Players (player_id, group_id) values (45, 1);
insert into 59_Players (player_id, group_id) values (50, 2);
Truncate table 59_Matches;
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (1, 15, 45, 3, 0);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (2, 30, 25, 1, 2);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (3, 30, 15, 2, 0);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (4, 40, 20, 5, 2);
insert into 59_Matches (match_id, first_player, second_player, first_score, second_score) values (5, 35, 50, 1, 1);

最终SQL:

select 
      group_id,
      player_id
from 
    (select 
           group_id,
           player_id,
           sum(
               case
                   when player_id = first_player then first_score
                   when player_id = second_player then second_score
               end
               ) as totalScores
     from 
          59_Players p,
          59_Matches m
     where
          p.player_id = m.first_player or
          p.player_id = m.second_player
     group by
          group_id,
          player_id
     order by
          group_id,
          totalScores desc,
          player_id
    ) as temp
group by 
     group_id
order by 
     group_id,
     totalScores desc,
     player_id;

60. Last Person to Fit in the Elevator

需求：查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯 。

展示效果：

person_name
Thomas Jefferson
Create table If Not Exists 60_Queue (person_id int, person_name varchar(30), weight int, turn int);
Truncate table 60_Queue;
insert into 60_Queue (person_id, person_name, weight, turn) values (5, 'George Washington', 250, 1);
insert into 60_Queue (person_id, person_name, weight, turn) values (4, 'Thomas Jefferson', 175, 5);
insert into 60_Queue (person_id, person_name, weight, turn) values (3, 'John Adams', 350, 2);
insert into 60_Queue (person_id, person_name, weight, turn) values (6, 'Thomas Jefferson', 400, 3);
insert into 60_Queue (person_id, person_name, weight, turn) values (1, 'James Elephant', 500, 6);
insert into 60_Queue (person_id, person_name, weight, turn) values (2, 'Will Johnliams', 200, 4);

最终SQL:

select 
      person_name
from 
      60_Queue q1
where 
     (select
           sum(weight)
      from
           60_Queue q
      where turn <= q1.turn) <= 1000
order by 
      turn desc 
limit 1;

61. Queries Quality and Percentage

需求：编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

展示效果：

query_namequalitypoor_query_percentage
Dog2.5033.33
Cat0.6633.33
Create table If Not Exists 61_Queries (query_name varchar(30), result varchar(50), position int, rating int);
Truncate table 61_Queries;
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', 1, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', 2, 5);
insert into 61_Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', 1);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', 5, 2);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Siamese', 3, 3);
insert into 61_Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', 7, 4);

最终SQL:

select
      query_name,
      round(avg(rating/position), 2) as quality ,
      round((count(if(rating<3, True, null)) / count(query_name)) *100 , 2) as poor_query_percentage
from
      61_Queries
group by 
      query_name
order by query_name desc;

62. Team Scores in Football Tournament

需求一：写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

展示效果：

team_idteam_namenum_points
10Leetcode FC7
20NewYork FC3
50Toronto FC3
30Atlanta FC1
40Chicago FC0
Create table If Not Exists 62_Teams (team_id int, team_name varchar(30));
Create table If Not Exists 62_Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table 62_Teams;
insert into 62_Teams (team_id, team_name) values (10, 'Leetcode FC');
insert into 62_Teams (team_id, team_name) values (20, 'NewYork FC');
insert into 62_Teams (team_id, team_name) values (30, 'Atlanta FC');
insert into 62_Teams (team_id, team_name) values (40, 'Chicago FC');
insert into 62_Teams (team_id, team_name) values (50, 'Toronto FC');
Truncate table 62_Matches;
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (1, 10, 20, 30, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (2, 30, 10, 2, 2);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (3, 10, 50, 5, 1);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (4, 20, 30, 1, 0);
insert into 62_Matches (match_id, host_team, guest_team, host_goals, guest_goals) values (5, 50, 30, 1, 0);

最终SQL:

SELECT 
     *
FROM
    (SELECT 
           a.team_id,
           MAX(team_name) AS team_name,
           SUM(
                CASE 
			        WHEN a.team_id = b.host_team THEN 
				    CASE 
					    WHEN b.host_goals > b.guest_goals THEN 3
					    WHEN b.host_goals = b.guest_goals THEN 1
			            ELSE 0
				    END
			        WHEN a.team_id = b.guest_team THEN 
				    CASE 
					    WHEN b.host_goals < b.guest_goals THEN 3
					    WHEN b.host_goals = b.guest_goals THEN 1
					    ELSE 0
				    END
			        ELSE 0
		       END
           ) AS num_points
	FROM 
         62_Teams a
    LEFT JOIN
         62_Matches b
    ON 
         a.team_id = b.host_team OR 
         a.team_id = b.guest_team
	GROUP BY a.team_id
    ) a 
ORDER BY
    a.num_points DESC,
    a.team_id;

63. 报告系统状态的连续日期

需求：系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。最后结果按照起始日期 start_date 排序。

展示效果：

period_statestart dateend date
present2019-01-012019-01-03
missing2019-01-042019-01-05
present2019-01-062019-01-06
Create table If Not Exists 63_Failed (fail_date date);
Create table If Not Exists 63_Succeeded (success_date date);
Truncate table 63_Failed;
insert into 63_Failed (fail_date) values ('2018-12-28');
insert into 63_Failed (fail_date) values ('2018-12-29');
insert into 63_Failed (fail_date) values ('2019-01-04');
insert into 63_Failed (fail_date) values ('2019-01-05');
Truncate table 63_Succeeded;
insert into 63_Succeeded (success_date) values ('2018-12-30');
insert into 63_Succeeded (success_date) values ('2018-12-31');
insert into 63_Succeeded (success_date) values ('2019-01-01');
insert into 63_Succeeded (success_date) values ('2019-01-02');
insert into 63_Succeeded (success_date) values ('2019-01-03');
insert into 63_Succeeded (success_date) values ('2019-01-06');

最终SQL:

select 
      if(str=1,'succeeded','failed') as period_state ,
      min(date) as start_date,
      max(date) as end_date
from 
     (select 
            @diff := @diff+ if(num = 1 , 1,0) as diff,
            date,
            str
      from
           (select 
                  case 
                      when @str = str and  date_add(@pre,interval 1 day) = date  then @num := @num +1
                      when @str:=str then  @num := 1
                      else @num := 1
                  end as num,
                  @pre := date,
                  date,
                  str
            from 
                 (select 
                        fail_date as date ,
                        0 as 'str'
                  from 
                        63_Failed 
                  union  
                  select
                        success_date,
                        1
                  from 
                        63_Succeeded 
                 ) s,
                 (select @pre:=null,@num:=0,@str := null) s1
            where 
                  date between '2019-01-01' and '2019-12-31'
            order by
                  date 
           ) s,
           (select @diff:=0)  s1
     ) ys
group by diff, str;

64. 每个帖子的评论数

需求一：编写 SQL 语句以查找每个帖子的评论数。结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

展示效果：

post_idnumber_of_comments
13
22
120
Create table If Not Exists 64_Submissions (sub_id int, parent_id int);
Truncate table 64_Submissions;
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (2, null);
insert into 64_Submissions (sub_id, parent_id) values (1, null);
insert into 64_Submissions (sub_id, parent_id) values (12, null);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (5, 2);
insert into 64_Submissions (sub_id, parent_id) values (3, 1);
insert into 64_Submissions (sub_id, parent_id) values (4, 1);
insert into 64_Submissions (sub_id, parent_id) values (9, 1);
insert into 64_Submissions (sub_id, parent_id) values (10, 2);
insert into 64_Submissions (sub_id, parent_id) values (6, 7);

最终SQL:

SELECT
	  post_id,
	  COUNT( DISTINCT S2.sub_id ) AS number_of_comments 
FROM
	(SELECT
           DISTINCT sub_id AS post_id 
     FROM 
           64_Submissions
     WHERE 
           parent_id IS NULL
    ) S1
LEFT JOIN
     64_Submissions S2
ON
     S1.post_id = S2.parent_id 
GROUP BY
     S1.post_id;

65. Average Selling Price

需求一：Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places.

The query result format is in the following example:

展示效果：

product_idaverage_price
16.96
216.96
Create table If Not Exists 65_Prices (product_id int, start_date date, end_date date, price int);
Create table If Not Exists 65_UnitsSold (product_id int, purchase_date date, units int);
Truncate table 65_Prices;
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-02-17', '2019-02-28', 5);
insert into 65_Prices (product_id, start_date, end_date, price) values (1, '2019-03-01', '2019-03-22', 20);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-01', '2019-02-20', 15);
insert into 65_Prices (product_id, start_date, end_date, price) values (2, '2019-02-21', '2019-03-31', 30);
Truncate table 65_UnitsSold;
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-02-25', 100);
insert into 65_UnitsSold (product_id, purchase_date, units) values (1, '2019-03-01', 15);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-02-10', 200);
insert into 65_UnitsSold (product_id, purchase_date, units) values (2, '2019-03-22', 30);

最终SQL:

select
      product_id,
      round(sum(a)/sum(units),2) as average_price
from
   (select 
          p.product_id as product_id,
          price,units,
          price * units as a
    from 
          65_Prices p 
    left join
          65_UnitsSold u
    on 
          p.product_id=u.product_id and 
          purchase_date<=end_date and 
          purchase_date>=start_date
   )t
group by 
    product_id;
龍浮影
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值