MySQL查询转换：将一行多列结果转为一列多行

Hey there! Turning those two columns into two rows is actually straightforward with UNION ALL—no overly complex subqueries required (though we’ll structure things cleanly here). Here’s exactly what you need:

SELECT SUM(idHit = 122791) AS hit_count FROM hits
UNION ALL
SELECT SUM(idHit = 950521) AS hit_count FROM hits;

How this works: #

• The first query calculates the sum for idHit = 122791 and returns it as a single row with the column hit_count.
• The second query does the same calculation for idHit = 950521.
• UNION ALL stacks these two results vertically, giving you a single column with two rows (your desired 5 and 7).

If you want to add context so you know which sum maps to which idHit, you can include a descriptive label column:

SELECT '122791' AS target_id, SUM(idHit = 122791) AS hit_count FROM hits
UNION ALL
SELECT '950521' AS target_id, SUM(idHit = 950521) AS hit_count FROM hits;

This will return a more readable result set:

target_id | hit_count
----------|----------
122791    | 5
950521    | 7

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