I have built an RFID Race Timing system and all is functioning well but now I need to either insert or update the tblMovement entry.
我已经构建了一个RFID Race Timing系统并且运行良好但现在我需要插入或更新tblMovement条目。
When inserting I must use tagID, movementDate, checkDate. movementDate is the actual tag read time. checkDate is for my anti collision functionality. When inserting it must place the movementDate into @Lap1.
插入时我必须使用tagID,movementDate,checkDate。 movementDate是实际的标签读取时间。 checkDate用于我的防冲突功能。插入时必须将movementDate放入@ Lap1。
When updating I must locate the tagID and check if @Lap1, @Lap2, @Lap3, @Lap4 or @Lap5 have data. If @Lap3 has data then I must insert into @Lap4 etc.
更新时我必须找到tagID并检查@ Lap1,@ Lap2,@ Lap3,@ Lap4或@ Lap5是否有数据。如果@ Lap3有数据,那么我必须插入@ Lap4等。
My SQL search query which locates the tagID is functioning fine and it either Updates or Inserts based on the results. My biggest challenge is scanning each column to see which has data. I have source code but its too much for this window.....
我找到tagID的SQL搜索查询运行正常,它根据结果更新或插入。我最大的挑战是扫描每一列以查看哪些数据。我有源代码,但这个窗口太多了.....
1 个解决方案
#1
1
Rather than having 5 lap times, it would be good to break this out into a normalized structure. That would mean having a tblMovementLaps table, with the following structure.
而不是有5个单圈时间,最好将其分解为标准化结构。这意味着有一个tblMovementLaps表,具有以下结构。
CREATE TABLE dbo.tblMovementLaps(
[Id] INT NOT NULL IDENTITY(1,1),
[TagId] INT NOT NULL,
[MovementDate] DATETIME NOT NULL,
[CheckDate] DATETIME NOT NULL
)
You would insert a new record into tblMovement at the start of each race, and a new record into tblMovementLap every time you were recording a new lap. Then you would join between the two to review a race, with:
您可以在每场比赛开始时将新记录插入tblMovement,并在每次录制新圈时将新记录插入tblMovementLap。然后你会加入两者之间来审查一场比赛:
SELECT *
FROM [tblMovement] TM
JOIN [tblMovementLaps] TML
ON TM.[TagId] = TML.[Id]
ORDER BY TML.[Id] ASC
A big benefit of doing things this way - it lets you record races of infinite lap counts without breaking your structure or expanding your movement table into more and more columns. Also it lets you keep space in the movement table available for information that is truly unique to each race (the basis of normalization), like the racer ID, race date, etc.
以这种方式做事的一大好处 - 它可以让您记录无限圈数的比赛,而不会破坏您的结构或将您的移动表扩展到越来越多的列。此外,它还允许您在移动表中保留空间,以获取每个种族(标准化的基础)真正独特的信息,如赛车ID,比赛日期等。
All that said, if you REALLY wanted to continue to support your current structure, you could add a [CurrentLap] field to TblMovement. Always insert your lap time to that field. Set up an UPDATE trigger on your table to move that value into one of the @lap fields based on which are populated
总而言之,如果你真的想继续支持你当前的结构,可以在TblMovement中添加一个[CurrentLap]字段。始终将您的单圈时间插入该区域。在表上设置UPDATE触发器,将该值移动到基于填充的@lap字段之一
#1
1
Rather than having 5 lap times, it would be good to break this out into a normalized structure. That would mean having a tblMovementLaps table, with the following structure.
而不是有5个单圈时间,最好将其分解为标准化结构。这意味着有一个tblMovementLaps表,具有以下结构。
CREATE TABLE dbo.tblMovementLaps(
[Id] INT NOT NULL IDENTITY(1,1),
[TagId] INT NOT NULL,
[MovementDate] DATETIME NOT NULL,
[CheckDate] DATETIME NOT NULL
)
You would insert a new record into tblMovement at the start of each race, and a new record into tblMovementLap every time you were recording a new lap. Then you would join between the two to review a race, with:
您可以在每场比赛开始时将新记录插入tblMovement,并在每次录制新圈时将新记录插入tblMovementLap。然后你会加入两者之间来审查一场比赛:
SELECT *
FROM [tblMovement] TM
JOIN [tblMovementLaps] TML
ON TM.[TagId] = TML.[Id]
ORDER BY TML.[Id] ASC
A big benefit of doing things this way - it lets you record races of infinite lap counts without breaking your structure or expanding your movement table into more and more columns. Also it lets you keep space in the movement table available for information that is truly unique to each race (the basis of normalization), like the racer ID, race date, etc.
以这种方式做事的一大好处 - 它可以让您记录无限圈数的比赛,而不会破坏您的结构或将您的移动表扩展到越来越多的列。此外,它还允许您在移动表中保留空间,以获取每个种族(标准化的基础)真正独特的信息,如赛车ID,比赛日期等。
All that said, if you REALLY wanted to continue to support your current structure, you could add a [CurrentLap] field to TblMovement. Always insert your lap time to that field. Set up an UPDATE trigger on your table to move that value into one of the @lap fields based on which are populated
总而言之,如果你真的想继续支持你当前的结构,可以在TblMovement中添加一个[CurrentLap]字段。始终将您的单圈时间插入该区域。在表上设置UPDATE触发器,将该值移动到基于填充的@lap字段之一