能否将26次周统计SQL查询合并为单条？（FileMaker环境）

Absolutely, you can ditch the loop and handle this with a single SQL query in FileMaker—no need to repeat the same query 26 times manually or via scripting. Here's how to structure it:

Core Idea #

We'll generate a list of 26 weekly date ranges (current week + 25 prior weeks), then join this list to your table to count records per week in one go.

Full Query (FileMaker 16+) #

FileMaker 16 and later support Common Table Expressions (CTEs), which make the query cleaner:

WITH week_offsets AS (
    SELECT 0 AS offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
    SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
),
weekly_ranges AS (
    SELECT
        offset,
        -- Calculate week start (adjust logic below if your week starts on Sunday instead of Monday)
        DateAdd(
            DateAdd(Get(CurrentDate); - (DayOfWeek(Get(CurrentDate)) - 2); days);
            - offset * 7;
            days
        ) AS week_start,
        -- Calculate week end
        DateAdd(
            DateAdd(Get(CurrentDate); - (DayOfWeek(Get(CurrentDate)) - 2); days);
            (- offset * 7) + 6;
            days
        ) AS week_end
    FROM week_offsets
)
SELECT
    week_start,
    week_end,
    COUNT(t.date_field) AS record_count
FROM weekly_ranges wr
LEFT JOIN your_table t 
    ON t.date_field >= wr.week_start 
    AND t.date_field <= wr.week_end
GROUP BY wr.offset, wr.week_start, wr.week_end
ORDER BY wr.offset ASC; -- 0 = current week, 25 = oldest week in range

For Older FileMaker Versions (Pre-16) #

If you're on a version before 16 (which doesn't support CTEs), rewrite the query using nested subqueries instead:

SELECT
    wr.week_start,
    wr.week_end,
    COUNT(t.date_field) AS record_count
FROM (
    SELECT
        offset,
        DateAdd(
            DateAdd(Get(CurrentDate); - (DayOfWeek(Get(CurrentDate)) - 2); days);
            - offset * 7;
            days
        ) AS week_start,
        DateAdd(
            DateAdd(Get(CurrentDate); - (DayOfWeek(Get(CurrentDate)) - 2); days);
            (- offset * 7) + 6;
            days
        ) AS week_end
    FROM (
        SELECT 0 AS offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
        SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
        SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
        SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
        SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25
    ) AS week_offsets
) AS wr
LEFT JOIN your_table t 
    ON t.date_field >= wr.week_start 
    AND t.date_field <= wr.week_end
GROUP BY wr.offset, wr.week_start, wr.week_end
ORDER BY wr.offset ASC;

Key Adjustments & Notes #

• Week Start/End Logic: FileMaker's DayOfWeek() returns 1 for Sunday, 2 for Monday, ..., 7 for Saturday.
  • If your week starts on Sunday, change - (DayOfWeek(Get(CurrentDate)) - 2) to - (DayOfWeek(Get(CurrentDate)) - 1) in the date calculations.
• Time Handling: If date_field includes time values (e.g., 2018-04-14 23:59:59), using <= week_end will miss records with times after midnight on the end date. Fix this by adding a week_end_plus_one field to weekly_ranges:

  DateAdd(week_end; 1; days) AS week_end_plus_one
  

  Then update the join condition to:

  ON t.date_field >= wr.week_start AND t.date_field < wr.week_end_plus_one
  

• Empty Weeks: Using LEFT JOIN ensures weeks with no records will still show up in results with a record_count of 0, which is better than omitting them entirely.

This query will return 26 rows (one per week) with the start date, end date, and count of records—exactly what you need, without any looping.

内容的提问来源于stack exchange，提问作者Chuck