Welcome everyone

每周一练(14)

每周一练 汪明鑫 236浏览 0评论

格式化部门收入表

部门表 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
;

 

Id Num Id Num Id Num
1 1 2 1 3 1

注意:前两列来自 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)

喜欢 (0)

说点什么

您将是第一位评论人!

提醒
avatar
wpDiscuz