LeetCode题解(1384):按年度列出销售总额(SQL)
发布日期:2021-06-29 20:12:03
浏览次数:3
分类:技术文章
本文共 2938 字,大约阅读时间需要 9 分钟。
题目:(困难)
标签:SQL
解法 | 时间复杂度 | 空间复杂度 | 执行用时 |
---|---|---|---|
Ans 1 (Python) | 450ms (5.06%) | ||
Ans 2 (Python) | |||
Ans 3 (Python) |
解法一:
SELECT T.product_id, P.product_name, T.report_year, T.total_amountFROM (SELECT product_id, '2018' AS report_year, CASE WHEN period_end < '2018-01-01' OR '2018-12-31' < period_start THEN 0 WHEN period_start < '2018-01-01' AND '2018-12-31' < period_end THEN 365 * average_daily_sales WHEN period_start < '2018-01-01' AND period_end < '2018-12-31' THEN (DATEDIFF(period_end, '2018-01-01') + 1) * average_daily_sales WHEN '2018-01-01' < period_start AND '2018-12-31' < period_end THEN (DATEDIFF('2018-12-31', period_start) + 1) * average_daily_sales WHEN '2018-01-01' < period_start AND period_end < '2018-12-31' THEN (DATEDIFF(period_end, period_start) + 1) * average_daily_sales END AS total_amount FROM Sales UNION ALL SELECT product_id, '2019' AS report_year, CASE WHEN period_end < '2019-01-01' OR '2019-12-31' < period_start THEN 0 WHEN period_start < '2019-01-01' AND '2019-12-31' < period_end THEN 365 * average_daily_sales WHEN period_start < '2019-01-01' AND period_end < '2019-12-31' THEN (DATEDIFF(period_end, '2019-01-01') + 1) * average_daily_sales WHEN '2019-01-01' < period_start AND '2019-12-31' < period_end THEN (DATEDIFF('2019-12-31', period_start) + 1) * average_daily_sales WHEN '2019-01-01' < period_start AND period_end < '2019-12-31' THEN (DATEDIFF(period_end, period_start) + 1) * average_daily_sales END AS total_amount FROM Sales UNION ALL SELECT product_id, '2020' AS report_year, CASE WHEN period_end < '2020-01-01' OR '2020-12-31' < period_start THEN 0 WHEN period_start < '2020-01-01' AND '2020-12-31' < period_end THEN 366 * average_daily_sales WHEN period_start < '2020-01-01' AND period_end < '2020-12-31' THEN (DATEDIFF(period_end, '2020-01-01') + 1) * average_daily_sales WHEN '2020-01-01' < period_start AND '2020-12-31' < period_end THEN (DATEDIFF('2019-12-31', period_start) + 1) * average_daily_sales WHEN '2020-01-01' < period_start AND period_end < '2020-12-31' THEN (DATEDIFF(period_end, period_start) + 1) * average_daily_sales END AS total_amount FROM Sales) AS T LEFT JOIN Product AS P ON P.product_id = T.product_idWHERE total_amount > 0ORDER BY product_id, report_year;
转载地址:https://dataartist.blog.csdn.net/article/details/110422366 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
表示我来过!
[***.240.166.169]2024年04月11日 14时08分50秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
SSD:虽然我适应多尺度,可是数据扩增对我可太重要了/(ㄒoㄒ)/~~
2019-04-30
关于 tflite 更新的 task_api 和 interpreter 两种方式
2019-04-30
2021-06-21 VC++ 6.0安装
2019-04-30
Java方法重载
2019-04-30
可变参数列表与数组作为参数的区别
2019-04-30
关于javadoc命令
2019-04-30
Java内存管理之堆和栈
2019-04-30
就近原则总结
2019-04-30
this关键字的应用
2019-04-30
构造方法常见问题总结
2019-04-30
package和import应用的常见问题
2019-04-30
static关键字的应用
2019-04-30
关于Objecg的常见问题
2019-04-30
关于单例模式的常见问题
2019-04-30
IDEA创建直接创建spring项目失败:下载失败 ‘https://repo1.maven.org/maven2/org/springframework/spring-aop/5.2.
2019-04-30
iOS推送证书过期处理,极光推送
2019-04-30
QT数据类型转换篇
2019-04-30