

□やりたいこと  
下記の'name'ごとに'value'列を分割して、再表示する際のSQLクエリ文についてご教授いただけますでしょうか。。

□mysqlバージョン

[code]

    +------------+
    | version()  |
    +------------+
    | 5.1.73-log |
    +------------+
    
[/code]

□現状の出力結果

[code]

    select from_unixtime(history.clock, '%Y-%m-%d %H:%i'), hosts.host, items.name, history.value from history
    inner join items using( itemid )
    inner join hosts using( hostid )
    where hosts.host like '%hoge%' 
    and items.name like '%Temp%' 
    and from_unixtime(clock) between '2019-03-07 00:00' and '2020-03-08 00:00'
    
    +------------------------------+-------------------+-----------+----------+
    | from_unixtime(history.clock) | host              | name      | value    |
    +------------------------------+-------------------+-----------+----------+
    | 2019-12-07 00:02             | hoge1             | Temp_CPU0 | -99.0000 |
    | 2019-12-07 00:07             | hoge1             | Temp_CPU0 | -98.0000 |
    | 2019-12-07 00:02             | hoge1             | Temp_CPU1 | -97.0000 |
    | 2019-12-07 00:07             | hoge1             | Temp_CPU1 | -96.0000 |
    | 2019-12-07 00:02             | hoge2             | Temp_CPU0 | -95.0000 |
    | 2019-12-07 00:07             | hoge2             | Temp_CPU0 | -94.0000 |
    | 2019-12-07 00:02             | hoge2             | Temp_CPU1 | -93.0000 |
    | 2019-12-07 00:07             | hoge2             | Temp_CPU1 | -92.0000 |
    
[/code]

□期待する出力結果

[code]

    +------------------------------+-------------------+----------+----------+
    | from_unixtime(history.clock) | host              | Temp_CPU0| Temp_CPU1
    +------------------------------+-------------------+----------+----------+
    | 2019-12-07 00:02             | hoge1             | -99.0000 | -97.0000 |
    | 2019-12-07 00:07             | hoge1             | -98.0000 | -96.0000 |
    | 2019-12-07 00:02             | hoge2             | -95.0000 | -93.0000 |
    | 2019-12-07 00:07             | hoge2             | -94.0000 | -92.0000 |
    
[/code]


CC BY-SA 4.0
2020-03-08T05:35:40.060
63657
2020-03-14T16:06:38.303
2020-03-08T06:06:42.750
4236
34125
question
mysql
select結果を共通項目ごとに列分割して、再出力させたい


【回答】  
任意の'name'ごとに'value'を列挙するSQLではありませんが、`Temp_CPU0`、`Temp_CPU1`固定の'value'を１行にまとめるSQLの記述は可能です。  
副問い合わせを使用します。

【副問い合わせを使用したSQL】

[code]

    select from_unixtime(CPU0.clock, '%Y-%m-%d %H:%i'),CPU0.host, CPU0.value as Temp_CPU0, CPU1.value as Temp_CPU1
      from
      (
         select xx.clock, xx.host, xx.name, xx.value
         from
         (
          select history.clock, hosts.host, items.name, history.value
          from history
          inner join items using( itemid )
          inner join hosts using( hostid )
          where hosts.host like '%hoge%' 
              and items.name like '%Temp%' 
              and from_unixtime(clock) between '2019-03-07 00:00' and '2020-03-08 00:00'
         ) xx
         where name like '%Temp_CPU0%'
      ) CPU0
      ,
      (
         select xx.clock, xx.host, xx.name, xx.value
         from
         (
          select history.clock, hosts.host, items.name, history.value
          from history
          inner join items using( itemid )
          inner join hosts using( hostid )
          where hosts.host like '%hoge%' 
              and items.name like '%Temp%' 
              and from_unixtime(clock) between '2019-03-07 00:00' and '2020-03-08 00:00'
         ) xx
         where name like '%Temp_CPU1%'
      ) CPU1
      where
        CPU0.host  = CPU1.host and
        CPU0.clock = CPU1.clock
    ;
    
[/code]

【WITH句を使用したSQL】  
MySQL 8.0以降でないと使えませんが、WITH句を使用しても同じことができます。

[code]

    with
      source as (
        select history.clock, hosts.host, items.name, history.value
        from history
        inner join items using( itemid )
        inner join hosts using( hostid )
        where hosts.host like '%hoge%' 
              and items.name like '%Temp%' 
              and from_unixtime(clock) between '2019-03-07 00:00' and '2020-03-08 00:00'
      ),
      cpu0 as (
        select clock, host, name, value from source where name like '%Temp_CPU0%'
      ),
      cpu1 as (
        select clock, host, name, value from source where name like '%Temp_CPU1%'
      )
    select from_unixtime(cpu0.clock, '%Y-%m-%d %H:%i'),cpu0.host,cpu0.value as Temp_CPU0, cpu1.value as Temp_CPU1
      from 
       (select clock, host, name, value from source where name like '%Temp_CPU0%') cpu0
       ,
       (select clock, host, name, value from source where name like '%Temp_CPU1%') cpu1
      where
        cpu0.host  = cpu1.host and
        cpu0.clock = cpu1.clock
    ;
    
[/code]


CC BY-SA 4.0
2020-03-14T16:06:38.303
63838
2020-03-14T16:06:38.303
35558
63657
answer