MySQL如何统计行中包含指定值的列数？

To solve this problem, we can calculate the number of 'A's for each row by checking each column individually and summing up the matches. Here's a straightforward approach:

MySQL Query #

SELECT 
    ID,
    (day1 = 'A') + (day2 = 'A') + (day3 = 'A') + (day4 = 'A') AS Count
FROM xyz;

How It Works #

• In MySQL, boolean conditions like day1 = 'A' return 1 when true and 0 when false. Adding these values together gives the total number of columns containing 'A' for each row.
• We use the alias Count to match your expected output format.

Expected Result #

Running this query will return exactly the result you're looking for:

ID Count
1  2
2  3
3  4
4  1

Alternative Explicit Version (Using CASE Statements) #

If you prefer more explicit logic (useful if you need to handle NULLs or complex conditions later), you can use CASE statements instead:

SELECT 
    ID,
    CASE WHEN day1 = 'A' THEN 1 ELSE 0 END +
    CASE WHEN day2 = 'A' THEN 1 ELSE 0 END +
    CASE WHEN day3 = 'A' THEN 1 ELSE 0 END +
    CASE WHEN day4 = 'A' THEN 1 ELSE 0 END AS Count
FROM xyz;

Both queries produce identical results—choose whichever fits your readability preference.

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