题目地址

https://leetcode.cn/problems/friday-purchases-ii/description/

代码

# Write your MySQL query statement below

WITH RECURSIVE Fridays (week_of_month, purchase_date) AS (
  -- Initial query to find the first Friday of the month
  SELECT 
    1 AS week_of_month,
    (SELECT DATE_ADD(DATE_ADD(LAST_DAY(CONCAT('2023-11-', '01')) + INTERVAL 1 DAY, INTERVAL -1 MONTH),
                     INTERVAL (4 - WEEKDAY(DATE_ADD(LAST_DAY(CONCAT('2023-11-', '01')) + INTERVAL 1 DAY, INTERVAL -1 MONTH))) DAY)) AS purchase_date.   -- 这里4对应的是控制周五
  
  UNION ALL
  
  -- Recursive query to find all subsequent Fridays of the month
  SELECT
    week_of_month + 1,
    DATE_ADD(purchase_date, INTERVAL 7 DAY)
  FROM Fridays
  WHERE MONTH(DATE_ADD(purchase_date, INTERVAL 7 DAY)) = 11   -- 这里可以理解为是：判断递归是否终止的条件语句
)
,
t1 as(
    SELECT
    *,
    DAYOFMONTH(purchase_date) as day_of_month,
    DAYOFWEEK(purchase_date) as day_of_week,
    FLOOR((DAYOFMONTH(purchase_date) + DAYOFWEEK(CONCAT(YEAR(purchase_date), '-', MONTH(purchase_date), '-01')) - 2) / 7) + 1 as week_of_month
    FROM
    Purchases
)
,
t2 as(
    select week_of_month,  purchase_date,
    sum(ifnull(amount_spend,0)) as total_amount
    from t1
    where day_of_week=6 #周五
    and date_format(purchase_date, "%Y-%m") ="2023-11"
    group by week_of_month,  purchase_date
    order by week_of_month asc
)


-- Final selection to get all Fridays of November 2023
SELECT f.week_of_month, f.purchase_date,
ifnull(t2.total_amount ,0) as total_amount
FROM Fridays f
left join t2
on f.week_of_month=t2.week_of_month and f.purchase_date=t2.purchase_date
order by f.week_of_month asc