MySQL一对多关联表（Task与Message）统计查询方案咨询

Hey there! Let's work through the statistical query solutions for the one-to-many relationship between your Task and Message tables in MySQL. First, let's recap your table structures to make sure we're aligned:

Task Table #

Message Table #

Below are common statistical query scenarios you might need, with explanations and code snippets:

1. Basic: Total Messages & Status Breakdown Per Task #

This is the most common use case—get a count of total messages per task, plus a breakdown of how many fall into each status category. We use LEFT JOIN to ensure tasks with no messages are still included in results.

SELECT
    t.id AS task_id,
    t.title,
    COUNT(m.id) AS total_messages,
    COUNT(CASE WHEN m.status = 'HOLD' THEN 1 END) AS hold_count,
    COUNT(CASE WHEN m.status = 'OK' THEN 1 END) AS ok_count,
    COUNT(CASE WHEN m.status = 'ERROR' THEN 1 END) AS error_count
FROM Task t
LEFT JOIN Message m ON t.id = m.task_id
GROUP BY t.id, t.title
ORDER BY t.id;

• LEFT JOIN keeps all tasks in the result set, even if they have no associated messages.
• The CASE WHEN clauses only return a value when the status matches, and COUNT tallies those non-null values to get per-status counts.

2. Filter Tasks by Specific Statuses #

Sometimes you need to narrow down tasks based on their message statuses. Here are two common filters:

2.1 Tasks with At Least One ERROR Message #

Use EXISTS for better performance than DISTINCT (especially with large datasets):

SELECT
    t.id AS task_id,
    t.title
FROM Task t
WHERE EXISTS (
    SELECT 1 FROM Message m
    WHERE m.task_id = t.id AND m.status = 'ERROR'
);

2.2 Tasks with No ERROR Messages (All OK/HOLD) #

Again, NOT EXISTS is efficient here:

SELECT
    t.id AS task_id,
    t.title
FROM Task t
WHERE NOT EXISTS (
    SELECT 1 FROM Message m
    WHERE m.task_id = t.id AND m.status = 'ERROR'
);

3. Advanced: Status Percentage Per Task #

If you want to see what percentage of each task's messages fall into each status, you can calculate that with basic arithmetic:

SELECT
    t.id AS task_id,
    t.title,
    COUNT(m.id) AS total_messages,
    ROUND((COUNT(CASE WHEN m.status = 'HOLD' THEN 1 END) / IFNULL(COUNT(m.id), 1)) * 100, 2) AS hold_percent,
    ROUND((COUNT(CASE WHEN m.status = 'OK' THEN 1 END) / IFNULL(COUNT(m.id), 1)) * 100, 2) AS ok_percent,
    ROUND((COUNT(CASE WHEN m.status = 'ERROR' THEN 1 END) / IFNULL(COUNT(m.id), 1)) * 100, 2) AS error_percent
FROM Task t
LEFT JOIN Message m ON t.id = m.task_id
GROUP BY t.id, t.title
ORDER BY t.id;

• IFNULL(COUNT(m.id), 1) prevents division-by-zero errors for tasks with no messages.
• ROUND(..., 2) formats the percentage to two decimal places for readability.

4. Aggregate Stats Across All Tasks #

If you want high-level metrics for all tasks combined:

SELECT
    COUNT(DISTINCT t.id) AS total_tasks,
    COUNT(m.id) AS total_messages_all_tasks,
    COUNT(CASE WHEN m.status = 'HOLD' THEN 1 END) AS total_hold,
    COUNT(CASE WHEN m.status = 'OK' THEN 1 END) AS total_ok,
    COUNT(CASE WHEN m.status = 'ERROR' THEN 1 END) AS total_error,
    ROUND(COUNT(m.id) / COUNT(DISTINCT t.id), 2) AS avg_messages_per_task
FROM Task t
LEFT JOIN Message m ON t.id = m.task_id;

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