SQL查询需求：按机器ID统计各Result状态数量（1-218号机）

Hey Tommy, great question! Since you're working with a high-volume table (500k records per shift) and need a full overview of every machine's status counts, let's go over a clean, efficient solution that avoids running dozens of separate queries.

Core Approach: Conditional Aggregation with Grouping #

Instead of writing individual counts for each machine and status, we can use conditional aggregation to compute all four status counts in a single query. This cuts down on table scans and is way more efficient for large datasets.

Basic Query (for machines with existing records) #

First, here's a query that groups by machine ID (Op) and calculates each status count in one go:

SELECT
  Op AS MachineID,
  COUNT(CASE WHEN Result = 0 THEN 1 END) AS OK_Count,
  COUNT(CASE WHEN Result = 1 THEN 1 END) AS Defective_Count,
  COUNT(CASE WHEN Result = 2 THEN 1 END) AS EmptyPallet_Count,
  COUNT(CASE WHEN Result = 3 THEN 1 END) AS FreeFlow_Count
FROM PalletOperations
GROUP BY Op
ORDER BY Op;

• COUNT(CASE ...) works because it only counts non-null values: when the Result matches the condition, it returns 1 (counted), otherwise it returns NULL (ignored).
• This will return all machines that have at least one record in the table, ordered by machine ID.

Including All Machines (1-218), Even Those With No Data #

If you need to include machines that haven't sent any data yet (so their counts show 0), you'll first need a full list of machine IDs, then join it to your aggregated data. We can use a recursive CTE to generate this list (works in most modern SQL databases like SQL Server, PostgreSQL, MySQL 8+):

WITH MachineIDs AS (
  SELECT 1 AS MachineID
  UNION ALL
  SELECT MachineID + 1
  FROM MachineIDs
  WHERE MachineID < 218
)
SELECT
  m.MachineID,
  COALESCE(p.OK_Count, 0) AS OK_Count,
  COALESCE(p.Defective_Count, 0) AS Defective_Count,
  COALESCE(p.EmptyPallet_Count, 0) AS EmptyPallet_Count,
  COALESCE(p.FreeFlow_Count, 0) AS FreeFlow_Count
FROM MachineIDs m
LEFT JOIN (
  -- Reuse the aggregation logic from the basic query
  SELECT
    Op AS MachineID,
    COUNT(CASE WHEN Result = 0 THEN 1 END) AS OK_Count,
    COUNT(CASE WHEN Result = 1 THEN 1 END) AS Defective_Count,
    COUNT(CASE WHEN Result = 2 THEN 1 END) AS EmptyPallet_Count,
    COUNT(CASE WHEN Result = 3 THEN 1 END) AS FreeFlow_Count
  FROM PalletOperations
  GROUP BY Op
) p ON m.MachineID = p.MachineID
ORDER BY m.MachineID;

• MachineIDs recursively generates numbers from 1 to 218 to cover every machine.
• LEFT JOIN ensures every machine from the list is included, even if there's no matching data in PalletOperations.
• COALESCE replaces NULL values (from machines with no data) with 0 for cleaner results.

Performance Tip for Large Datasets #

Since your table has 500k records per shift, adding a composite index on (Op, Result) will drastically speed up this query by letting the database quickly group and count without scanning the entire table:

CREATE INDEX IX_PalletOperations_Op_Result ON PalletOperations (Op, Result);

This index is tailored exactly for grouping by Op and filtering on Result, which is what our aggregation query does.

Quick Notes #

• If your database has a built-in sequence generator (like PostgreSQL's generate_series(1,218)), you can replace the recursive CTE with that for simpler code.
• If you only care about machines that have at least one record, stick with the first query—it's slightly faster since it avoids the join step.

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