mysql - 在 mysql v3 中将行转换为列

Question

根据此代码，对于 locationid 5,6,7,8，它是否可能位于第二行而不是继续？

SELECT
    MAX(case when locationid = '1' then floatvalue end) Flow,
    MAX(case when locationid = '2' then floatvalue end) Level,
    MAX(case when locationid = '3' then floatvalue end) Pressure,
    MAX(case when locationid = '4' then floatvalue end) MR,  
    MAX(case when locationid = '5' then floatvalue end) Flow,
    MAX(case when locationid = '6' then floatvalue end) Level,
    MAX(case when locationid = '7' then floatvalue end) Pressure,
    MAX(case when locationid = '8' then floatvalue end) MR
FROM table

所以代替这个:

Flow | Level | Pressure | MR | Flow | Level | Pressure | MR  |
10   |  20   |   30     | 40 | 100  |  200  |   300    | 400 |

它会像这样:

Flow | Level | Pressure | MR  |
 10  |  20   |   30     | 40  |
 100 |  200  |   300    | 400 |

Answer 1

您正在寻找这样的东西吗？

SELECT MAX(CASE WHEN locationid = 1 THEN floatvalue END) Flow,
       MAX(CASE WHEN locationid = 2 THEN floatvalue END) Level,
       MAX(CASE WHEN locationid = 3 THEN floatvalue END) Pressure,
       MAX(CASE WHEN locationid = 4 THEN floatvalue END) MR
  FROM table1
 WHERE locationid IN(1, 2, 3, 4)
UNION ALL
SELECT MAX(CASE WHEN locationid = 5 THEN floatvalue END),
       MAX(CASE WHEN locationid = 6 THEN floatvalue END),
       MAX(CASE WHEN locationid = 7 THEN floatvalue END),
       MAX(CASE WHEN locationid = 8 THEN floatvalue END)
  FROM table1
 WHERE locationid IN(5, 6, 7, 8) 

这里是SQLFiddle 演示