你好,我有这种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
为了将来的信息,我已经在这里创建了sql fiddle
谢谢!
1条答案
按热度按时间3ks5zfa01#
你可以试试这个。聚合函数条件
使用
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