表 Department
:
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ 在 SQL 中,(id, month) 是表的联合主键。 这个表格有关于每个部门每月收入的信息。 月份(month)可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
重新格式化表格,使得 每个月 都有一个部门 id 列和一个收入列。
以 任意顺序 返回结果表。
结果格式如以下示例所示。
示例 1:
输入: Department table: +------+---------+-------+ | 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 列用于各个月份)。
select
a.id,
Jan_Revenue,
Feb_Revenue,
Mar_Revenue,
Apr_Revenue,
May_Revenue,
Jun_Revenue,
Jul_Revenue,
Aug_Revenue,
Sep_Revenue,
Oct_Revenue,
Nov_Revenue,
Dec_Revenue
from
(select
distinct id
from
department) a
left join
(select
distinct id,
revenue Jan_Revenue
from
department
where month='Jan') b
on a.id=b.id
left join
(select
distinct id,
revenue Feb_Revenue
from
department
where month='Feb') c
on a.id=c.id
left join
(select
distinct id,
revenue Mar_Revenue
from
department
where month='Mar') d
on a.id=d.id
left join
(select
distinct id,
revenue Apr_Revenue
from
department
where month='Apr') e
on a.id=e.id
left join
(select
distinct id,
revenue May_Revenue
from
department
where month='May') f
on a.id=f.id
left join
(select
distinct id,
revenue Jun_Revenue
from
department
where month='Jun') g
on a.id=g.id
left join
(select
distinct id,
revenue Jul_Revenue
from
department
where month='Jul') h
on a.id=h.id
left join
(select
distinct id,
revenue Aug_Revenue
from
department
where month='Aug') i
on a.id=i.id
left join
(select
distinct id,
revenue Sep_Revenue
from
department
where month='Sep') j
on a.id=j.id
left join
(select
distinct id,
revenue Oct_Revenue
from
department
where month='Oct') k
on a.id=k.id
left join
(select
distinct id,
revenue Nov_Revenue
from
department
where month='Nov') l
on a.id=l.id
left join
(select
distinct id,
revenue dec_Revenue
from
department
where month='dec') m
on a.id=m.id
标签:格式化,Revenue,distinct,练习,month,revenue,sql,id,select
From: https://blog.csdn.net/JYXCCC/article/details/139769036