SQL表格形式转化例题

列转行

eg.1179.重新格式化部门表

部门表 Department： | Column Name | Type | | ----------- | ---- | | id | int | | revenue | int | | month | varchar |

(id, month) 是表的联合主键。这个表格有关于每个部门每月收入的信息。月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

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

查询结果格式如下面的示例所示：

Department 表：

id revenue month

1 8000 Jan

2 9000 Jan

3 10000 Feb

1 7000 Feb

1 6000 Mar

查询得到的结果表： | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | | ---- | ----------- | ----------- | ----------- | ---- | ----------- | | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null |

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

id	revenue	month
1	8000	Jan
2	9000	Jan
3	10000	Feb
1	7000	Feb
1	6000	Mar

题解：

select
    id, 
    max(if(month='Jan', revenue, NULL)) as Jan_Revenue,
    max(if(month='Feb', revenue, NULL)) as Feb_Revenue,
    max(if(month='Mar', revenue, NULL)) as Mar_Revenue,
    max(if(month='Apr', revenue, NULL)) as Apr_Revenue,
    max(if(month='May', revenue, NULL)) as May_Revenue,
    max(if(month='Jun', revenue, NULL)) as Jun_Revenue,
    max(if(month='Jul', revenue, NULL)) as Jul_Revenue,
    max(if(month='Aug', revenue, NULL)) as Aug_Revenue,
    max(if(month='Sep', revenue, NULL)) as Sep_Revenue,
    max(if(month='Oct', revenue, NULL)) as Oct_Revenue,
    max(if(month='Nov', revenue, NULL)) as Nov_Revenue,
    max(if(month='Dec', revenue, NULL)) as Dec_Revenue
from Department
group by id
order by id

group by之后使用if过滤

行转列

eg.1795.每个产品在不同商店的价格

表：Products | Column Name | Type | | ----------- | ---- | | product_id | int | | store1 | int | | store2 | int | | store3 | int |

这张表的主键是product_id（产品Id）。每行存储了这一产品在不同商店store1, store2, store3的价格。如果这一产品在商店里没有出售，则值将为null。

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。输出结果表中的顺序不作要求。

查询输出格式请参考下面示例。

示例 1：

输入： Products table: | product_id | store1 | store2 | store3 | | ---------- | ------ | ------ | ------ | | 0 | 95 | 100 | 105 | | 1 | 70 | null | 80 |

输出： | product_id | store | price | | ---------- | ----- | ----- | | 0 | store1 | 95 | | 0 | store2 | 100 | | 0 | store3 | 105 | | 1 | store1 | 70 | | 1 | store3 | 80 |

解释：

产品0在store1，store2,store3的价格分别为95,100,105。

产品1在store1，store3的价格分别为70,80。在store2无法买到。

题解：

select 
    product_id, 
    'store1' as store,
    max(store1) as price
from Products
where store1 is not NULL
group by product_id
union all
select 
    product_id, 
    'store2' as store,
    max(store2) as price
from Products
where store2 is not NULL
group by product_id
union all
select 
    product_id, 
    'store3' as store,
    max(store3) as price
from Products
where store3 is not NULL
group by product_id

利用union all拼接不同情况
利用where过滤不同情况

数列构建

eg.1613.找到遗失的ID

表: Customers | Column Name | Type | | ----------- | ---- | | customer_id | int | | customer_name | varchar |

customer_id 是该表主键.该表第一行包含了顾客的名字和id.

写一个 SQL 语句, 找到所有遗失的顾客id. 遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.

注意: 最大的 customer_id 值不会超过 100.返回结果按 ids 升序排列

查询结果格式如下例所示.

Customers 表: | customer_id | customer_name | | ----------- | ------------- | | 1 | Alice | | 4 | Bob | | 5 | Charlie |

Result 表:

ids

2

3

表中最大的customer_id是5, 所以在范围[1,5]内, ID2和3从表中遗失.

ids
2
3

题解：

with recursive temp(n) as (
    select 1
    union all
    select n+1
    from temp
    where n <(
        select max(customer_id)
        from Customers
    )
) # 利用with递归查询构造id范围

select t.n as ids
from temp as t
where t.n not in (
    select customer_id
    from Customers
)

eg.1767.寻找没有被执行的任务对

表：Tasks | Column Name | Type | | ----------- | ---- | | task_id | int | | subtasks_count | int |

task_id 是这个表的主键。task_id 表示的为主任务的id,每一个task_id被分为了多个子任务(subtasks)，subtasks_count表示为子任务的个数（n），它的值表示了子任务的索引从1到n。本表保证2 <=subtasks_count<= 20。

表： Executed | Column Name | Type | | ----------- | ---- | | task_id | int | | subtask_id | int |

(task_id, subtask_id) 是这个表的主键。每一行表示标记为task_id的主任务与标记为subtask_id的子任务被成功执行。本表保证，对于每一个task_id，subtask_id <= subtasks_count。请试写一个SQL查询语句报告没有被执行的（主任务，子任务）对，即没有被执行的（task_id, subtask_id）。

以任何顺序返回即可。

查询结果格式如下。

示例 1：

输入：

Tasks 表:

task_id subtasks_count

1 3

2 2

3 4

Executed 表: | task_id | subtask_id | | ------- | ---------- | | 1 | 2 | | 3 | 1 | | 3 | 2 | | 3 | 3 | | 3 | 4 |

输出： | task_id | subtask_id | | ------- | ---------- | | 1 | 1 | | 1 | 3 | | 2 | 1 | | 2 | 2 |

解释：

Task 1 被分成了 3 subtasks (1, 2, 3)。只有 subtask 2 被成功执行, 所以我们返回 (1, 1) 和 (1, 3) 这两个主任务子任务对。

Task 2 被分成了 2 subtasks (1, 2)。没有一个subtask被成功执行, 因此我们返回(2, 1)和(2, 2)。

Task 3 被分成了 4 subtasks (1, 2, 3, 4)。所有的subtask都被成功执行，因此对于Task 3,我们不返回任何值。

task_id	subtasks_count
1	3
2	2
3	4

题解：

with recursive temp(task_id, subtask_id) as(
    select task_id, subtasks_count as subtask_id
    from Tasks
    union all
    select task_id, subtask_id-1
    from temp
    where subtask_id > 1
) # 主要注意递归查询的书写

select t.task_id as task_id, t.subtask_id as subtask_id
from temp as t
left join Executed as e
on t.task_id = e.task_id and t.subtask_id = e.subtask_id
where e.subtask_id is NULL
order by task_id, subtask_id

字符串构建

eg.1384.按年度列出销售总额

Product 表： | Column Name | Type | | ----------- | ---- | | product_id | int | | product_name | varchar |

product_id 是这张表的主键。product_name 是产品的名称。

Sales 表： | Column Name | Type | | ----------- | ---- | | product_id | int | | period_start | date | | period_end | date | | average_daily_sales | int |

product_id 是这张表的主键。period_start 和 period_end 是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。average_daily_sales 列存储销售期内该产品的日平均销售额。

编写一段 SQL 查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。

销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。

查询结果格式如下例所示：

Product table: | product_id | product_name | | ---------- | ------------ | | 1 | LC Phone | | 2 | LC T-Shirt | | 3 | LC Keychain |

Sales table: | product_id | period_start | period_end | average_daily_sales | | ---------- | ------------ | ---------- | ------------------- | | 1 | 2019-01-25 | 2019-02-28 | 100 | | 2 | 2018-12-01 | 2020-01-01 | 10 | | 3 | 2019-12-01 | 2020-01-31 | 1 |

Result table: | product_id | product_name | report_year | total_amount | | ---------- | ------------ | ----------- | ------------ | | 1 | LC Phone | 2019 | 3500 | | 2 | LC T-Shirt | 2018 | 310 | | 2 | LC T-Shirt | 2019 | 3650 | | 2 | LC T-Shirt | 2020 | 10 | | 3 | LC Keychain | 2019 | 31 | | 3 | LC Keychain | 2020 | 31 |

题解：

with years as (
    select
        '2018' as year,
        '2018-01-01' as year_start,
        '2018-12-31' as year_end
    union
    select
        '2019' as year,
        '2019-01-01' as year_start,
        '2019-12-31' as year_end
    union
    select
        '2020' as year,
        '2020-01-01' as year_start,
        '2020-12-31' as year_end
) # 列出时间范围框架表

select 
    temp.product_id as product_id,
    p.product_name as product_name,
    temp.year as report_year,
    (datediff(temp.end,temp.start) + 1) * temp.avg_sales as total_amount
from (
    select #列出每个产品在每一年的销售时间
        s.product_id as product_id, y.year as year, average_daily_sales as avg_sales,
        if(s.period_start>y.year_start, s.period_start, y.year_start) as start,
        if(s.period_end<y.year_end, s.period_end, y.year_end) as end
    from Sales as s
    left join years as y
    on not(s.period_end < y.year_start or s.period_start > year_end)
) as temp
join Product as p
on temp.product_id = p.product_id
order by product_id, report_year

eg.1635.Hopper公司查询

表: Drivers | Column Name | Type | | ----------- | ---- | | driver_id | int | | join_date | date |

driver_id是该表的主键。该表的每一行均包含驾驶员的ID以及他们加入Hopper公司的日期。

表: Rides | Column Name | Type | | ----------- | ---- | | ride_id | int | | user_id | int | | requested_at | date |

ride_id是该表的主键。该表的每一行均包含行程ID(ride_id)，用户ID(user_id)以及该行程的日期(requested_at)。该表中可能有一些不被接受的乘车请求。

表: AcceptedRides | Column Name | Type | | ----------- | ---- | | ride_id | int | | driver_id | int | | ride_distance | int | | ride_duration | int |

ride_id是该表的主键。该表的每一行都包含已接受的行程信息。表中的行程信息都在“Rides”表中存在。

编写SQL查询以报告2020年每个月的以下统计信息：

截至某月底，当前在Hopper公司工作的驾驶员数量（active_drivers）。该月接受的乘车次数（accepted_rides）。返回按month 升序排列的结果表，其中month 是月份的数字（一月是1，二月是2，依此类推）。

查询结果格式如下例所示。

表 Drivers: | driver_id | join_date | | --------- | --------- | | 10 | 2019-12-10 | | 8 | 2020-1-13 | | 5 | 2020-2-16 | | 7 | 2020-3-8 | | 4 | 2020-5-17 | | 1 | 2020-10-24 | | 6 | 2021-1-5 |

表 Rides: | ride_id | user_id | requested_at | | ------- | ------- | ------------ | | 6 | 75 | 2019-12-9 | | 1 | 54 | 2020-2-9 | | 10 | 63 | 2020-3-4 | | 19 | 39 | 2020-4-6 | | 3 | 41 | 2020-6-3 | | 13 | 52 | 2020-6-22 | | 7 | 69 | 2020-7-16 | | 17 | 70 | 2020-8-25 | | 20 | 81 | 2020-11-2 | | 5 | 57 | 2020-11-9 | | 2 | 42 | 2020-12-9 | | 11 | 68 | 2021-1-11 | | 15 | 32 | 2021-1-17 | | 12 | 11 | 2021-1-19 | | 14 | 18 | 2021-1-27 |

表 AcceptedRides: | ride_id | driver_id | ride_distance | ride_duration | | ------- | --------- | ------------- | ------------- | | 10 | 10 | 63 | 38 | | 13 | 10 | 73 | 96 | | 7 | 8 | 100 | 28 | | 17 | 7 | 119 | 68 | | 20 | 1 | 121 | 92 | | 5 | 7 | 42 | 101 | | 2 | 4 | 6 | 38 | | 11 | 8 | 37 | 43 | | 15 | 8 | 108 | 82 | | 12 | 8 | 38 | 34 | | 14 | 1 | 90 | 74 |

结果表: | month | active_drivers | accepted_rides | | ----- | -------------- | -------------- | | 1 | 2 | 0 | | 2 | 3 | 0 | | 3 | 4 | 1 | | 4 | 4 | 0 | | 5 | 5 | 0 | | 6 | 5 | 1 | | 7 | 5 | 1 | | 8 | 5 | 1 | | 9 | 5 | 0 | | 10 | 6 | 0 | | 11 | 6 | 2 | | 12 | 6 | 1 |

截至1月底->两个活跃的驾驶员（10,8），没有被接受的行程。

截至2月底->三个活跃的驾驶员（10,8,5），没有被接受的行程。

截至3月底->四个活跃的驾驶员（10,8,5,7），一个被接受的行程（10）。

截至4月底->四个活跃的驾驶员（10,8,5,7），没有被接受的行程。

截至5月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。

截至6月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（13）。

截至7月底->五个活跃的驾驶员（10,8,5,7,4），一个被接受的行程（7）。

截至8月底->五个活跃的驾驶员（10,8,5,7,4），一位接受的行程（17）。

截至9月底->五个活跃的驾驶员（10,8,5,7,4），没有被接受的行程。

截至10月底->六个活跃的驾驶员（10,8,5,7,4,1），没有被接受的行程。

截至11月底->六个活跃的驾驶员（10,8,5,7,4,1），两个被接受的行程（20,5）。

截至12月底->六个活跃的驾驶员（10,8,5,7,4,1），一个被接受的行程（2）。

题解：

with temp as (
    select '2020-1-31' as end_date
    union all
    select '2020-2-29' as end_date
    union all
    select '2020-3-31' as end_date
    union all
    select '2020-4-30' as end_date
    union all    
    select '2020-5-31' as end_date
    union all
    select '2020-6-30' as end_date
    union all
    select '2020-7-31' as end_date
    union all 
    select '2020-8-31' as end_date
    union all 
    select '2020-9-30' as end_date
    union all 
    select '2020-10-31' as end_date
    union all 
    select '2020-11-30' as end_date
    union all 
    select '2020-12-31' as end_date  
) # 构建每个月份的最终截止日期


select 
    month(t1.end_date) as month,
    any_value(t1.active_drivers) as active_drivers,
    sum(if(t2.ride_date is NULL, 0, 1)) as accepted_rides
from(
    select # 查询每个月的活跃司机数
        t.end_date as end_date, 
        count(if(d.join_date <= t.end_date, 1, NULL)) as active_drivers
    from temp as t 
    join Drivers as d
    group by t.end_date
) as t1
left join(
    select # 查询接受的行程的具体时间
    	date_format(r.requested_at,'%Y-%m') as ride_date
    from AcceptedRides as a
    left join Rides as r
    on a.ride_id = r.ride_id
) as t2
on date_format(t1.end_date, '%Y-%m') = t2.ride_date
group by t1.end_date
order by month

数据库 > SQL

#SQL #LeetCode #数据库

SQL表格形式转化例题

http://example.com/2022/03/10/SQL表格形式转化例题/

作者

EverNorif

发布于

2022年3月10日

许可协议

HBase学习笔记-入门(1)-HBase基本介绍上一篇

SQL表格形式转化问题技巧汇总下一篇