sql创建一个视图,其中包含添加行的计算列

62o28rlo  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(339)

我在mysql中有一个表,记录了特定进程的开始时间和结束时间。看起来是这样的:

process_id process_start       process_end         data_1   data_2
         1 2017-12-05 15:21:55 2017-12-05 15:39:48 SD12891  8952020517397180000
         2 2017-12-05 15:40:19 2017-12-05 21:56:10 40001067 8952020017390610000
         3 2017-12-05 21:58:10 2017-12-05 22:04:46 SD12667  89014103279450400000
         4 2017-12-05 22:05:50 2017-12-05 22:08:11 40001067 89014103279450400000

我想创建这个db的一个视图,用一个名为“process\u time”的新列来记录进程的小时数[left(process\u start,13)],但是它会为进程启动的每一小时创建一行(这意味着它会为process\u start和process\u end之间的每一小时创建一新行)和每一新行,其他列中的所有值应保持不变。所以上一张图片的分贝是这样的:

process_id process_time  data_1   data_2
         1 2017-12-05 15 SD12891  8952020517397180000
         2 2017-12-05 15 40001067 8952020017390610000
         2 2017-12-05 16 40001067 8952020017390610000
         2 2017-12-05 17 40001067 8952020017390610000
         2 2017-12-05 18 40001067 8952020017390610000
         2 2017-12-05 19 40001067 8952020017390610000
         2 2017-12-05 20 40001067 8952020017390610000
         2 2017-12-05 21 40001067 8952020017390610000
         3 2017-12-05 21 SD12667  89014103279450400000
         3 2017-12-05 22 SD12667  89014103279450400000
         4 2017-12-05 22 40001067 89014103279450400000

怎么做这样的事?

uwopmtnx

uwopmtnx1#

一旦你有了 numbers table,这很简单。。。
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0101c14c048f313b69405a77ad62caee

SELECT
  rounded.process_id,
  rounded.process_start,
  rounded.process_end,
  TIMESTAMPADD(HOUR, numbers.id, rounded.process_start_hour)   AS process_hour,
  rounded.data_1,
  rounded.data_2
FROM
(
  SELECT
    *,
    DATE_FORMAT(process_start,'%Y-%m-%d %H:00:00')   process_start_hour,
    DATE_FORMAT(process_end  ,'%Y-%m-%d %H:00:00')   process_end_hour
  FROM
    document
)
  rounded
INNER JOIN
  numbers
    ON numbers.id <= EXTRACT(HOUR FROM TIMEDIFF(process_end_hour, process_start_hour))
ORDER BY
  rounded.process_id,
  numbers.id

我强烈建议在任何情况下,如果您要将其中的一行转换为可能的行,使用数字表。

相关问题