题目地址

https://leetcode.cn/problems/total-sales-amount-by-year/description/

代码

WITH RECURSIVE DateSeries AS (
    SELECT product_id, period_start AS sale_date, period_end, average_daily_sales
    FROM Sales -- Assuming your table name is sales_data
    UNION ALL
    SELECT product_id, DATE_ADD(sale_date, INTERVAL 1 DAY), period_end, average_daily_sales
    FROM DateSeries
    WHERE sale_date < period_end
)

, YearlySales AS (
    SELECT 
        product_id,
        date_format(sale_date,"%Y") AS report_year,
        COUNT(DISTINCT sale_date) AS days_sold,
        SUM(average_daily_sales) AS total_amount
    FROM DateSeries
    GROUP BY product_id, YEAR(sale_date)
)

SELECT 
    y.product_id, 
    p.product_name, 
    y.report_year, 
    y.total_amount
FROM YearlySales y
JOIN Product p ON y.product_id = p.product_id -- Assuming there's a 'products' table with product_name
ORDER BY y.product_id, y.report_year;