WITH RECURSIVE
months(month_date) AS (
SELECT DATE_FORMAT('2025-08-01', '%Y-%m-01')
UNION ALL
SELECT DATE_ADD(month_date, INTERVAL -1 MONTH)
FROM months
WHERE month_date > DATE_ADD(DATE_FORMAT('2025-08-01', '%Y-%m-01'), INTERVAL -11 MONTH)
),
month_range AS (
SELECT
m.month_date,
m.month_date AS first_day,
LAST_DAY(m.month_date) AS last_day
FROM months m
),
first_monday AS (
SELECT
mr.month_date,
DATE_ADD(mr.first_day, INTERVAL ((7 - WEEKDAY(mr.first_day)) % 7) DAY) AS week_start
FROM month_range mr
),
last_sunday AS (
SELECT
mr.month_date,
DATE_SUB(mr.last_day, INTERVAL ((WEEKDAY(mr.last_day) + 1) % 7) DAY) AS week_end
FROM month_range mr
),
weeks AS (
SELECT
fm.month_date,
fm.week_start
FROM first_monday fm
UNION ALL
SELECT
w.month_date,
DATE_ADD(w.week_start, INTERVAL 7 DAY)
FROM weeks w
JOIN month_range mr ON w.month_date = mr.month_date
WHERE DATE_ADD(w.week_start, INTERVAL 7 DAY) <= mr.last_day
),
-- 最终结果：排除周开始 > 周结束的无效数据
weeks_series AS (SELECT
w.week_start AS `week_start`,
CASE
WHEN DATE_ADD(w.week_start, INTERVAL 6 DAY) > ls.week_end THEN ls.week_end
ELSE DATE_ADD(w.week_start, INTERVAL 6 DAY)
END AS `week_end`
FROM weeks w
JOIN last_sunday ls ON w.month_date = ls.month_date
WHERE w.week_start <= ls.week_end ),
team_series AS ( SELECT '69bc9b7376de3b30b35bc43e63a6ab2e' AS teamId UNION ALL SELECT '2dc1e35e0433d86f75b1fe95b8c89b6a' UNION ALL SELECT '5138bb98e789e8cae2635b07c6058ce3' UNION ALL SELECT '5d1b805db3e5e62bb917653a61d88ad8' UNION ALL SELECT '8626ace8dc484e2eb2a3d5c4301a6505' UNION ALL SELECT 'ef4a920ef92a8f73ef05c6730529b810' ),
pre_meeting AS (
SELECT
DATE_FORMAT( date, '%Y-%m' ),
teamId,
team_name,
unit_name,
sum( num )
FROM
(
SELECT
date( start_time ) AS date,
t.teamId,
p.team_name,
p.unit_name,
count( DISTINCT p.team_id ) AS num
FROM
team_series t
LEFT JOIN ics_pre_shift_meeting p ON t.teamId = p.team_id
WHERE
flag = 1
AND start_time >= DATE_ADD( DATE_FORMAT( NOW(), '%Y-%m-01' ), INTERVAL - 11 MONTH )
GROUP BY
date( start_time ),
t.teamId,
p.team_name,
p.unit_name
) a
GROUP BY
DATE_FORMAT( date, '%Y-%m' ),
teamId,
team_name,
unit_name
),after_meeting AS (
SELECT
DATE_FORMAT( date, '%Y-%m' ),
teamId,
team_name,
unit_name,
sum( num )
FROM
(
SELECT
date( time ) AS date,
t.teamId,
a.team_name,
a.unit_name,
count( DISTINCT a.team_id ) AS num
FROM
team_series t
LEFT JOIN ics_after_class_meeting a ON t.teamId = a.team_id
WHERE
time >= DATE_ADD( DATE_FORMAT( NOW(), '%Y-%m-01' ), INTERVAL - 11 MONTH )
GROUP BY
date( time ),
t.teamId,
a.team_name,
a.unit_name
) b
GROUP BY
DATE_FORMAT( date, '%Y-%m' ),
teamId,
team_name,
unit_name
)
SELECT
*
FROM
team_series t
LEFT JOIN ics_team_activities ita ON t.teamId = ita.team_id
LEFT JOIN ( SELECT week_start, week_end FROM weeks_series WHERE ( SELECT week_end FROM last_sunday ) >= week_start ) w ON ita.time BETWEEN w.week_start
AND w.week_end
报错提示 returns more than 1 row
最新发布
扫一扫
757
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
