如何将表中的属性引用到同一表中新行中的值 [英] How do I reference an attribute inside a table to a value inside a new row within the same table

问题描述

我正在尝试创建一个表格，其中包含许多运动员的历史跑步时间（以分钟为单位）。该表包含人员名称的外键，并存储其新的运行时间和以前的运行时间以及运行的日期。

I am trying to create a table which holds the historical running times (in minutes) for a host of athletes. The table holds a foreign key to the persons name, along with storing their new running time and previous running time, along with the date the run was performed.

我试图将所有跑步者记录保留在同一张桌子中。我想在新的运行时间完成时在新条目中引用旧的运行时间。我正在为这种关系如何运作而苦苦挣扎。

I am trying to keep all records of runners in the same table. I want to refer to the old running time in the new entry of when a new running time is complete. I am struggling on how this relationship will work.

下面的表格解释了我要实现的目标。

Below is a table explaining what I am trying to achieve.

如表所示，2019年2月16日的新平均值显示为2019年3月16日的旧平均值。

As you the table shows, the new average from the 16/02/2019 appears as the old average in 16/03/2019.

我的问题是如何建立这种关系？有可能建立这种关系吗？

My question is how would I construct this relationship? Is it possible to make this a relationship?

或

有没有更有效的方法？即具有下表：

Is there a more efficient way? I.e Have the following table:

并创建一个查询，该查询可以使用Name_ID和completion_Date属性来生成如下输出：

and create a query that could use the Name_ID and completion_Date attributes to produce an output that made:

任何帮助将不胜感激。

推荐答案

如果没有Mysql 8.x服务器，您可以使用它。

If you don't have a Mysql 8.x Server you can use this.

CREATE TABLE table1
(`Name_ID {FK}` int, `Completion_Date` varchar(10), `New_Time` int)
;

INSERT INTO table1
(`Name_ID {FK}`, `Completion_Date`, `New_Time`)
VALUES
(001, '16/01/2019', 108),
(001, '16/02/2019', 123),
(001, '16/03/2019', 136)
;

您可以使用此

select  `Name_ID {FK}`,`Completion_Date`,@quot old_time, @quot:=`New_Time` new_time
 from table1 p,(SELECT @quot := 0) r
 order by  `Name_ID {FK}`,`Completion_Date`;

得到以下结果：

Name_ID {FK}    Completion_Date     old_time    new_time
  1              16/01/2019            0           108
  1              16/02/2019            108         123
  1              16/03/2019            123         136

基于此
在MySQL中模拟延迟功能

这篇关于如何将表中的属性引用到同一表中新行中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！