我有一个表,其中的数据是通过指标垂直输入的,如下例所示

^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/

10-11 22:36
查看更多