格式化部门收入表

部门表 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个月份的收入列)。

select id,
       sum(case
               when month = 'Jan'
                   then revenue
           end) as Jan_Revenue,
       sum(case
               when month = 'Feb'
                   then revenue
           end) as Feb_Revenue,
       sum(case
               when month = 'Mar'
                   then revenue
           end) as Mar_Revenue,
       sum(case
               when month = 'Apr'
                   then revenue
           end) as Apr_Revenue,
       sum(case
               when month = 'May'
                   then revenue
           end) as May_Revenue,
       sum(case
               when month = 'Jun'
                   then revenue
           end) as Jun_Revenue,
       sum(case
               when month = 'Jul'
                   then revenue
           end) as Jul_Revenue,
       sum(case
               when month = 'Aug'
                   then revenue
           end) as Aug_Revenue,
       sum(case
               when month = 'Sep'
                   then revenue
           end) as Sep_Revenue,
       sum(case
               when month = 'Oct'
                   then revenue
           end) as Oct_Revenue,
       sum(case
               when month = 'Nov'
                   then revenue
           end) as Nov_Revenue,
       sum(case
               when month = 'Dec'
                   then revenue
           end) as Dec_Revenue
from department
group by id;

讲道理这种语法一般用不到的

连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+—-+—–+
| Id | Num |
+—-+—–+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+—-+—–+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+—————–+
| ConsecutiveNums |
+—————–+
| 1 |
+—————–+

# 这样写 不是连续出现的三次
select Num as ConsecutiveNums
from Logs 
group by Num
having count(num) >= 3; 

连续出现的有什么关联呢？

关联在于id是连续的 id1 = id2-1, id2=id3-1

使用联表查询

SELECT *
FROM
    Logs log1,
    Logs log2,
    Logs log3
WHERE
    log1.Id = log2.Id - 1
    AND log2.Id = log3.Id - 1
    AND log1.Num = log2.Num
    AND log2.Num = log3.Num
;

注意：前两列来自 log1 ，接下来两列来自 log2 ，最后两列来自 log3 。

然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。

SELECT DISTINCT
    log1.Num AS ConsecutiveNums
FROM
    Logs log1,
    Logs log2,
    Logs log3
WHERE
    log1.Id = log2.Id - 1
    AND log2.Id = log3.Id - 1
    AND log1.Num = log2.Num
    AND log2.Num = log3.Num
;

转载请注明：汪明鑫的个人博客 » 每周一练（14）