选择每行值已更改的列

wr98u20j 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(287)

我试图从一个mysql表中提取数据，并以一种日志文件格式插入到另一个表中。
假设我有这张table。
表

+--------+----+-------+-------+-------+---------+
|UniqueID|Item|ColumnA|ColumnB|ColumnC|TimeStamp|
+--------+----+-------+-------+-------+---------+
|1       | 1  | 500   | 600   | 700   |   13:01 |
|2       | 2  |  50   |  60   |  70   |   13:03 |
|3       | 3  |  17   |  18   |  19   |   13:12 |
|4       | 1  | 501   | 600   | 700   |   13:15 |
|5       | 1  | 501   | 600   | 699   |   13:18 |
|6       | 3  |  20   |  18   |  19   |   13:22 |
|7       | 1  | 501   | 600   | 702   |   13:25 |
|8       | 2  |  50   |  66   |  70   |   13:26 |
|9       | 3  |  20   |  25   |  19   |   13:32 |
+--------+----+-------+-------+-------+---------+

我将有多个表，列数和项数各不相同。我不介意硬编码表名和列名。
我想以这张table结束

+----+------+-------+--------+-------------+---------+
|Item|Table |Column |NewValue|PreviousValue|TimeStamp|
+----+------+-------+--------+-------------+---------+
| 1  |TableA|ColumnA| 501    | 500         | 13:15   |
| 1  |TableA|ColumnC| 699    | 700         | 13:18   |
| 3  |TableA|ColumnA|  20    |  17         | 13:22   |
| 1  |TableA|ColumnC| 699    | 702         | 13:25   |
| 2  |TableA|ColumnB|  66    |  60         | 13:26   |
| 3  |TableA|ColumnB|  25    |  18         | 13:32   |
+----+------+-------+--------+-------------+---------+

sql mysql

来源：https://stackoverflow.com/questions/53508417/select-a-column-where-value-have-changed-for-each-row

1条答案

按热度按时间

hgqdbh6s1#

mysql并不能让这变得很容易。您可以通过计算前一个时间戳并将其加入来获取前一个记录。在mysql 8.0中，您可以使用 lag() 为此目的。
然后你需要取消这些值。mysql并不能使这变得非常容易，但是您可以使用 cross join 以及 case 表达。剩下的只是条件逻辑和过滤：

select item, 'TableA' as tableName, colname,
       col as newvalue, col_prev as prevvalue, timestamp
from (select t.*, c.colname,
             (case when c.colname = 'columnA' then columnA
                   when c.colname = 'columnB' then columnB
                   when c.colname = 'columnC' then columnC
              end) as col,
             (case when c.colname = 'columnA' then tprev.columnA
                   when c.colname = 'columnB' then tprev.columnB
                   when c.colname = 'columnC' then tprev.columnC
              end) as col_prev
      from (select t.*,
                   (select max(t2.timestamp)
                    from tablea t2
                    where t2.item = t.item and t2.timestamp < t.timestamp
                   ) as timestamp_prev
            from tablea t
           ) t join
           t tprev
           on t.item = tprev.item and t.timestamp_prev = tprev.timestamp cross join
           (select 'columnA' as colname union all
            select 'columnB' as colname union all
            select 'columnC' as colname
           ) c
     ) ct
where col_prev <> col;

赞(0）回复(0）举报 2021-06-18

我来回答

选择每行值已更改的列

1条答案

相关问题

热门标签

最新问答