如何合并myo与cbp两张表按周分组的注射次数统计结果？

Alright, let's figure out how to combine those weekly injection counts from your cbp and myo tables. Based on what you've already got, there are two common ways to approach this—depending on whether you want to see separate counts for each table per week, or a total combined count.

Option 1: Show separate counts for each table per week #

If you want to break out how many injections came from cbp vs myo for each week (including weeks where only one table has data), use a full outer join between your two existing aggregated queries. We'll use COALESCE to turn any NULL values (for weeks where one table has no records) into 0, so you get a clean, complete weekly view:

SELECT
    COALESCE(c.week, m.week) AS week,
    COALESCE(c.count, 0) AS cbp_injections,
    COALESCE(m.count, 0) AS myo_injections
FROM
    (SELECT EXTRACT(week from cbp_date) as week, count(*) AS count
     FROM cbp 
     WHERE EXTRACT(year from cbp_date) = '2018' 
       AND cbp_injected_by = 'ABC' 
     GROUP BY week) c
FULL OUTER JOIN
    (SELECT EXTRACT(week from myo_date) as week, count(*) AS count
     FROM myo 
     WHERE EXTRACT(year from myo_date) = '2018' 
       AND myo_injected_by = 'ABC' 
     GROUP BY week) m
ON c.week = m.week
ORDER BY week ASC;

If your database (like MySQL) doesn't support FULL OUTER JOIN, use this workaround: first gather all unique weeks from both tables, then left join each aggregated query to that list:

WITH all_weeks AS (
    SELECT EXTRACT(week from cbp_date) as week FROM cbp 
    WHERE EXTRACT(year from cbp_date) = '2018' AND cbp_injected_by = 'ABC'
    UNION
    SELECT EXTRACT(week from myo_date) as week FROM myo 
    WHERE EXTRACT(year from myo_date) = '2018' AND myo_injected_by = 'ABC'
)
SELECT
    aw.week,
    COALESCE(c.count, 0) AS cbp_injections,
    COALESCE(m.count, 0) AS myo_injections
FROM all_weeks aw
LEFT JOIN (
    SELECT EXTRACT(week from cbp_date) as week, count(*) AS count
    FROM cbp 
    WHERE EXTRACT(year from cbp_date) = '2018' AND cbp_injected_by = 'ABC'
    GROUP BY week
) c ON aw.week = c.week
LEFT JOIN (
    SELECT EXTRACT(week from myo_date) as week, count(*) AS count
    FROM myo 
    WHERE EXTRACT(year from myo_date) = '2018' AND myo_injected_by = 'ABC'
    GROUP BY week
) m ON aw.week = m.week
ORDER BY aw.week ASC;

Option 2: Get a total combined count per week #

If you just need the total number of injections across both tables for each week, use UNION ALL to stack the two aggregated results, then group and sum them together:

SELECT
    week,
    SUM(injection_count) AS total_injections
FROM
    (SELECT EXTRACT(week from cbp_date) as week, count(*) AS injection_count
     FROM cbp 
     WHERE EXTRACT(year from cbp_date) = '2018' 
       AND cbp_injected_by = 'ABC' 
     GROUP BY week
     UNION ALL
     SELECT EXTRACT(week from myo_date) as week, count(*) AS injection_count
     FROM myo 
     WHERE EXTRACT(year from myo_date) = '2018' 
       AND myo_injected_by = 'ABC' 
     GROUP BY week) combined_results
GROUP BY week
ORDER BY week ASC;

Stick with UNION ALL instead of plain UNION here—it preserves all rows from both tables, so we can correctly sum counts for weeks that have data in both cbp and myo.

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