在阿里云不会用SQL语言处理数据

Question

SELECT `t0`.`CO4` AS `CO4`, `t0`.`CO1` AS `CO1`, `t0`.`CO3` AS `CO3`, `t0`.`COO2` AS `COO2`, `t0`.`$iot_id` AS iot_id
    , `t0`.`$product_key` AS product_key, from_unixtime(`t0`.`$event_time` / 1000, '%Y-%m-%d %H:%i:%S') AS t0_event_time
FROM `${2312343421}` t0
ORDER BY t0.$event_time DESC

通过上面的SQL语句我可以得到以下数据，但我如果想让数据改变下。

{
    "COO2": 772,
    "iot_id": "52353153535124",
    "product_key": "2312343421",
    "CO1": 939,
    "CO3": 1016,
    "CO4": 752,
    "t0_event_time": "2021-10-18 14:43:53"
  }

变成以下形式

[
  {
    "设备名称":"CO1",
    "数值”：936
    "iot_id": "52353153535124",
    "product_key": "2312343421",
    "t0_event_time": "2021-10-18 14:43:53"
  },
 {
    "设备名称":"COO2",
    "数值”：772
    "iot_id": "52353153535124",
    "product_key": "2312343421",
    "t0_event_time": "2021-10-18 14:43:53"
  },
 {
    "设备名称":"CO3",
    "数值”：1016
    "iot_id": "52353153535124",
    "product_key": "2312343421",
    "t0_event_time": "2021-10-18 14:43:53"
  },
 {
    "设备名称":"CO4",
    "数值”：752
    "iot_id": "52353153535124",
    "product_key": "2312343421",
    "t0_event_time": "2021-10-18 14:43:53"
  }
]

在阿里云用SQL处理传感器接收数据，这样该如何修改呢？大家能帮帮忙指点下吗？找了两三天也没有头绪。

Answer 1

用union all连接查。

select 设备名称,iot_id, product_key, t0_event_time from(
SELECT `t0`.`CO4` AS 设备名称, `t0`.`$iot_id` AS iot_id
    , `t0`.`$product_key` AS product_key, from_unixtime(`t0`.`$event_time` / 1000, '%Y-%m-%d %H:%i:%S') AS t0_event_time
FROM `${2312343421}` t0
union all

SELECT `t0`.`COO2` AS 设备名称, `t0`.`$iot_id` AS iot_id
    , `t0`.`$product_key` AS product_key, from_unixtime(`t0`.`$event_time` / 1000, '%Y-%m-%d %H:%i:%S') AS t0_event_time
FROM `${2312343421}` t0
union all

SELECT `t0`.`CO3` AS 设备名称,`t0`.`$iot_id` AS iot_id
    , `t0`.`$product_key` AS product_key, from_unixtime(`t0`.`$event_time` / 1000, '%Y-%m-%d %H:%i:%S') AS t0_event_time
FROM `${2312343421}` t0
union all
SELECT `t0`.`CO1` AS 设备名称,`t0`.`$iot_id` AS iot_id
    , `t0`.`$product_key` AS product_key, from_unixtime(`t0`.`$event_time` / 1000, '%Y-%m-%d %H:%i:%S') AS t0_event_time
FROM `${2312343421}` t0) t
ORDER BY t.t0_event_time DESC