MySQL Columns Into Rows

Question

我正试图根据表中的列创建一个列表，并对值求和。我有下表

id | box1 | box2 | box3
------------------------
111 | 1 | 0 | 1
111 | 0 | 1 | 1
111 | 1 | 1 | 1
111 | 0 | 0 | 1

我需要像这个一样的结果

column | sum
------------
box1 | 2
box2 | 2
box3 | 4

我试图将所有列合并并选择每一列，但我能够获得正确的列标题。我能得到总数，但我不知道如何得到列名。

SELECT SUM(box1) as box1Count FROMqueue
UNION ALL
SELECT SUM(box2) as box2Count FROM queue
UNION ALL
SELECT SUM(box3) as box3Count FROM queue

Answer 1

您可以对SELECT子句中的列值进行硬编码：

SELECT 'box1' AS col, SUM(box1) as box1Count FROM queue
UNION ALL
SELECT 'box2', SUM(box2) as box2Count FROM queue
UNION ALL
SELECT 'box3', SUM(box3) as box3Count FROM queue