本文共 2938 字，大约阅读时间需要 9 分钟。

题目：（困难）

标签：SQL

解法一：

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 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！