MySQL Pivot 将记录转为列出所有数据的列

Question

你好，我有这种 table 。

svcid hostid fieldname      fieldval                           date_chk
  1    9205    rawdata      raw data                           2018-07-27 05:14:47
  1    9205    rawdata      raw data                           2018-07-27 05:14:57
  1    9205    rawdata      raw data                           2018-07-27 05:20:24
  1    9205    dummyoutput  echo "you have running dummy.sh!"  2018-07-27 05:20:24
  1    9205    rawdata      raw data                           2018-07-27 05:21:04
  1    9205    dummyoutput  echo "you have running dummy.sh!"  2018-07-27 05:21:04

我想变成这样

svcid hostid  rawdata    dummyoutput                           date_chk
  1    9205   raw data   null                                  2018-07-27 05:14:47
  1    9205   raw data   null                                  2018-07-27 05:14:57
  1    9205   raw data   echo "you have running dummy.sh!"     2018-07-27 05:20:24
  1    9205   raw data   echo "you have running dummy.sh!"     2018-07-27 05:21:24

future 信息I already create sql fiddle in here

谢谢!

Answer 1

你可以试试这个。 聚合函数条件

将 CASE WHEN 与 MAX 函数和 group by 一起使用

SELECT svcid,
       hostid,
       max(CASE WHEN fieldname = 'rawdata' THEN fieldval  END), 
       max(CASE WHEN fieldname = 'dummyoutput' THEN fieldval  END),
       date_chk
FROM tb_service_out_monitor
GROUP BY  svcid,
       hostid,
       date_chk

sqlfiddle