LeetCode题解(1179):重新格式化部门表(SQL)
发布日期:2021-06-29 19:55:31
浏览次数:4
分类:技术文章
本文共 2809 字,大约阅读时间需要 9 分钟。
题目:(简单)
解法 | 执行用时 |
---|---|
Ans 1 (Python) | 409ms (38.77%) |
Ans 2 (Python) | 489ms (16.33%) |
Ans 3 (Python) | 422ms (33.30%) |
LeetCode的Python执行用时随缘,只要时间复杂度没有明显差异,执行用时一般都在同一个量级,仅作参考意义。
解法一:
SELECT id, SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue, SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue, SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue, SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue, SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue, SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue, SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue, SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue, SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue, SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue, SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue, SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_RevenueFROM DepartmentGROUP BY id;
解法二:
select id, sum(case when month = 'Jan' then revenue else null end) as 'Jan_Revenue', sum(case when month = 'Feb' then revenue else null end) as 'Feb_Revenue', sum(case when month = 'Mar' then revenue else null end) as 'Mar_Revenue', sum(case when month = 'Apr' then revenue else null end) as 'Apr_Revenue', sum(case when month = 'May' then revenue else null end) as 'May_Revenue', sum(case when month = 'Jun' then revenue else null end) as 'Jun_Revenue', sum(case when month = 'Jul' then revenue else null end) as 'Jul_Revenue', sum(case when month = 'Aug' then revenue else null end) as 'Aug_Revenue', sum(case when month = 'Sep' then revenue else null end) as 'Sep_Revenue', sum(case when month = 'Oct' then revenue else null end) as 'Oct_Revenue', sum(case when month = 'Nov' then revenue else null end) as 'Nov_Revenue', sum(case when month = 'Dec' then revenue else null end) as 'Dec_Revenue'from Departmentgroup by id;
解法三:
select id, max(case when month = 'Jan' then revenue end) Jan_Revenue, max(case when month = 'Feb' then revenue end) Feb_Revenue, max(case when month = 'Mar' then revenue end) Mar_Revenue, max(case when month = 'Apr' then revenue end) Apr_Revenue, max(case when month = 'May' then revenue end) May_Revenue, max(case when month = 'Jun' then revenue end) Jun_Revenue, max(case when month = 'Jul' then revenue end) Jul_Revenue, max(case when month = 'Aug' then revenue end) Aug_Revenue, max(case when month = 'Sep' then revenue end) Sep_Revenue, max(case when month = 'Oct' then revenue end) Oct_Revenue, max(case when month = 'Nov' then revenue end) Nov_Revenue, max(case when month = 'Dec' then revenue end) Dec_Revenuefrom Departmentgroup by id;
转载地址:https://dataartist.blog.csdn.net/article/details/107110014 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
能坚持,总会有不一样的收获!
[***.219.124.196]2024年04月17日 12时55分12秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
Linux ALSA声卡驱动之五:移动设备中的ALSA(ASoC)
2019-04-30
RT-thread相关
2019-04-30
Linux 2.6内核Makefile浅析
2019-04-30
编译和链接的过程
2019-04-30
Git学习(二):git-rev-parse命令初识
2019-04-30
vim字符串替换
2019-04-30
C语言:堆和栈的区别是什么?
2019-04-30
C语言:二级指针(指向指针的指针)详解
2019-04-30
C语言:断言assert函数完全攻略
2019-04-30
C语言:命令行选项解析函数---getopt()和getopt_long()
2019-04-30
C语言:inline,static inline
2019-04-30
Git学习(三):Git 撤销commit文件 和 回退push的文件
2019-04-30
WAV系列之一:G711编解码原理及代码实现
2019-04-30
WAV系列之二:ADPCM编解码原理及代码实现
2019-04-30
详解shell中source、sh、bash、./执行脚本的区别
2019-04-30
Git学习(四):git clean的用法
2019-04-30
Linux命令(一): ln - 创建和删除软、硬链接
2019-04-30
C语言:static关键字的作用
2019-04-30
C语言:volatile关键字的作用
2019-04-30