MySQL按risk分组统计总行数及equal列求和的SQL实现咨询

Hey there! Let's work through this SQL problem to get exactly the result set you need.

First, let's simplify how we calculate the equal column—your current correlated subquery is overcomplicating things. Since we just need to check if resultCode matches finalResult for each row, a simple CASE statement will do the trick far more efficiently.

We can wrap this logic in a subquery (since MySQL 5.6 doesn't support CTEs) and then group by risk to compute both the sum of equal (your corrects column) and total rows per risk group.

Here's the final SQL statement that will produce your desired output:

SELECT
  risk,
  SUM(equal) AS corrects,
  COUNT(*) AS total
FROM (
  SELECT
    risk,
    CASE WHEN resultCode = finalResult THEN 1 ELSE 0 END AS equal
  FROM matches
  WHERE finalResult IS NOT NULL
) AS subquery
GROUP BY risk
ORDER BY risk ASC;

Let's break this down step by step: #

1. Inner Subquery: This generates the equal column for every row—returning 1 if resultCode matches finalResult, 0 otherwise. We also filter out rows where finalResult is NULL as required.
2. Outer Query: We group the subquery results by risk:
   • SUM(equal) adds up all the 1s and 0s, giving us the count of matching rows per risk group (your corrects value).
   • COUNT(*) gives the total number of rows in each risk group (your total value).
3. Ordering: We sort the final results by risk ascending to match the format you provided.

Testing with your sample data: #

When run against your matches table, this query will output exactly the result set you specified:

If you really wanted to use your original correlated subquery for equal (though it's less efficient), you could adjust the query like this—but the first version is better for performance, especially as your table grows:

SELECT
  risk,
  SUM(equal) AS corrects,
  COUNT(*) AS total
FROM (
  SELECT
    risk,
    (SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END 
     FROM matches WHERE resultCode = t.finalResult AND oddId = t.oddId) AS equal
  FROM matches t
  WHERE finalResult IS NOT NULL
) AS subquery
GROUP BY risk
ORDER BY risk ASC;

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