LeetCode（数据库）- 按年度列出销售总额

待我称王封你为后i 2022-09-12 03:52 195阅读 0赞

**题目链接：**[点击打开链接][Link 1]

**题目大意：**略。

**解题思路：**略。

**AC 代码**

WITH t0 AS(SELECT *, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toY  FROM Sales WHERE DATE_FORMAT(period_start, '%Y') = DATE_FORMAT(period_end, '%Y')),
    t1 AS(SELECT *, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toY FROM Sales WHERE DATE_FORMAT(period_start, '%Y') - DATE_FORMAT(period_end, '%Y') = -1),
    t2 AS(SELECT *, DATE_FORMAT(period_start, '%Y') fromY, DATE_FORMAT(period_end, '%Y') toY  FROM Sales WHERE DATE_FORMAT(period_start, '%Y') - DATE_FORMAT(period_end, '%Y') = -2),
    
    t3 AS(SELECT product_id, fromY report_year, (DATEDIFF(period_end, period_start) + 1) * average_daily_sales total_amount
    FROM t0
    
    UNION ALL 
    
    SELECT product_id, fromY, (DATEDIFF(CONCAT(fromY, '-12-31'), period_start) + 1) * average_daily_sales total_amount
    FROM t1
    
    UNION ALL 
    
    SELECT product_id, toY, (DATEDIFF(period_end, CONCAT(toY, '-01-01')) + 1) * average_daily_sales total_amount
    FROM t1
    
    UNION ALL 
    
    SELECT product_id, fromY, (DATEDIFF(CONCAT(fromY, '-12-31'), period_start) + 1) * average_daily_sales total_amount
    FROM t2
    
    UNION ALL 
    
    SELECT product_id, fromY + 1, 365 * average_daily_sales total_amount
    FROM t2
    
    UNION ALL 
    
    SELECT product_id, toY, (DATEDIFF(period_end, CONCAT(toY, '-01-01')) + 1) * average_daily_sales total_amount
    FROM t2)
    
    SELECT CONCAT('', p.product_id) product_id, product_name, report_year, total_amount
    FROM t3 JOIN Product p ON p.product_id = t3.product_id
    ORDER BY product_id, report_year

[Link 1]: https://leetcode-cn.com/problems/customers-who-never-order/