主页 / dba / 问题 / 239709
Accepted
arilwan
Asked: 2019-06-04 07:45:08 +0800 CST

更改列中的值

  • 772

我手头有下表（用于数据迁移）：

recvtimets     |         recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+--------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2019-06-03T14:47:47.88Z  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2019-06-03T14:47:47.782Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2019-06-03T14:47:47.696Z | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

前两列描述了应用程序写入数据库的时间戳（从某个数据库读取）。然而，实际记录数据的时间是列中的dateObserved属性，attrname其对应的值是attrvalue列中显示的值。

这意味着前 6 行（2019-06-03T14:47:47.88Z在recvtime列中记录在2015-09-01T10:15:57+01:00（显示在 attrvalue列上），即dateObserved = 2015-09-01T10:15:57+01:00

2019-06-03T14:47:47.782Z列中的接下来的 6 行recvtime记录在 上2015-09-01T10:16:24+01:00，即dateObserved =2015-09-01T10:16:24+01:00

同样，最后 6 行有recvtimeas2019-06-03T14:47:47.696Z记录在2015-09-01T10:16:22+01:00iedateObserved = 2015-09-01T10:16:22+01:00

目标是更改recvtime列的内容，以便将这些值替换为记录为包含在attrvalue相应行中的日期。

因此，最终结果如下表所示（即产生此结果的最终表）：

recvtimets     |           recvtime         | fiwareservicepath | entityid |   entitytype    |     attrname     |    attrtype     |               attrvalue               | attrmd
---------------+----------------------------+-------------------+----------+-----------------+------------------+-----------------+---------------------------------------+--------
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.613502,41.143994]} | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 59.1                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:15:57+01:00             | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 24.9                                  | []
 1559573267088 | 2015-09-01T10:15:57+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Rua das Flores                        | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Praca do Marques - pole 1             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | temperature      | Number          | 21.9                                  | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:24+01:00             | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.603802,41.161671]} | []
 1559573267782 | 2015-09-01T10:16:24+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 80.8                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | name             | Text            | Fundacao de Serralves                 | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | illuminance      | None            | null                                  | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | location         | StructuredValue | {"coordinates":[-8.65915,41.1591]}    | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | relativeHumidity | Number          | 1                                     | []
 1559573267696 | 2015-09-01T10:16:22+01:00  | /basic            | 001      | WeatherObserved | dateObserved     | Text            | 2015-09-01T10:16:22+01:00             | []

我如何在 SQL 中实现这一点？

postgresql

1 个回答

  1. Best Answer
    SELECT t1.recvtimets, 
       t2.attrvalue recvtime,
       t1.fiwareservicepath,
       t1.entityid,
       t1.entitytype,
       t1.attrname,
       t1.attrtype,
       t1.attrvalue,
       t1.attrmd
FROM tablename t1
JOIN tablename t2 ON t1.recvtimets = t2.recvtimets
                 AND t2.attrname = 'dateObserved'
WHERE t1.attrname != 'dateObserved'
    • 2

相关问题