我有一个表,其中的数据是通过指标垂直输入的,如下例所示
^ID ^ idc1 ^ idc2 ^ idc3 ^
|1 | 7 | 11 | 5 |
|2 | 8 | 14 | 19 |
|3 | 9 | 22 | 4 |
我有一个查询,允许我将数据转换为水平结果。
但是,这个查询只给出了第一行,而不是全部3行
^ apples ^ Pears ^ Orange ^
| 7 | 8 | 9 | (idc1)
我想得到这个结果
^ apples ^ Pears ^ Orange ^
| 7 | 8 | 9 | (idc1)
| 11 | 14 | 22 | (idc2)
| 5 | 19 | 4 | (idc3)
以下是查询:
SELECT
sum( if( id = 1, idc1, 0 ) ) AS Apples,
sum( if( id = 2, idc1, 0 ) ) AS Pears,
sum( if( id = 3, idc1, 0 ) ) AS Orange
FROM
brf_vr
WHERE idcdate = '2018-11-27'
GROUP BY
idcdate;
有人知道吗
高级THX
干杯
最佳答案
试试这个,但我不推荐:
SELECT idcList,
MAX(CASE WHEN ID = 1 THEN value END) `Apple`,
MAX(CASE WHEN ID = 2 THEN value END) `Pears`,
MAX(CASE WHEN ID = 3 THEN value END) `Orange`
from
(
SELECT ID,
idcList,
CASE idcList
WHEN 'idc1' THEN idc1
WHEN 'idc2' THEN idc2
WHEN 'idc3' THEN idc3
END value
FROM mysql_test t
CROSS JOIN
(
SELECT 'idc1' idcList
UNION ALL
SELECT 'idc2'
UNION ALL
SELECT 'idc3'
) c
) q
GROUP BY idcList
ORDER BY FIELD(idcList, 'idc1', 'idc2', 'idc3')
如果您可以查看架构设计
关于php - 从mysql表转置数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53706630/