使用 SQL 中的子查询更新多行

发布于 2024-12-16 21:16:02 字数 526 浏览 0 评论 0原文

我有一个汇总表从主表返回数据。我正在尝试从主表中的值更新一些摘要数据，如下所示：

update #summary
  set TopSpeed = CD.TopSpeed, SpeedTime = CD.TimeSent, SpeedDriver = CD.Driver
from
(
  select top 1 TopSpeed, TimeSent, Driver
  from CarData
  where CarData.VehicleId = #summary.VehicleId 
  and CarData.TimeSent between #summary.Start and #summary.Stop
  order by CarData.TopSpeed desc, TimeSent desc
) as CD

#summary 临时表正在创建有关汽车行程的汇总数据。每辆车可以有多个行程，每个行程都有开始和停止时间。 CarData 表包含所有详细的汽车数据，如速度和位置等。

请问您能帮忙吗？

谢谢，罗伯特

原文

I have a summary table return data from a master table. I am trying to update some of the summary data from values in the master table as such:

update #summary
  set TopSpeed = CD.TopSpeed, SpeedTime = CD.TimeSent, SpeedDriver = CD.Driver
from
(
  select top 1 TopSpeed, TimeSent, Driver
  from CarData
  where CarData.VehicleId = #summary.VehicleId 
  and CarData.TimeSent between #summary.Start and #summary.Stop
  order by CarData.TopSpeed desc, TimeSent desc
) as CD

The #summary temporary table is creating summarised data about trips undertaken by a car. There can be multiple trips for each car with each trip having a start and stop time. The CarData table contains all the detailed car data like speed and position etc.

Please can you help?

Thanks,
Robert

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

输什么也不输骨气 2024-12-23 21:16:02

试试这个：

UPDATE
    #summary
SET
    #summary.TopSpeed = CarData.TopSpeed ,
    #summary.SpeedTime = CarData.SpeedTime 
    ...
FROM
    #summary
INNER JOIN
    CarData
ON
    #summary.id = CarData.id AND (CarData.TimeSent BETWEEN #summary.Start AND #summary.Stop)

Try this:

UPDATE
    #summary
SET
    #summary.TopSpeed = CarData.TopSpeed ,
    #summary.SpeedTime = CarData.SpeedTime 
    ...
FROM
    #summary
INNER JOIN
    CarData
ON
    #summary.id = CarData.id AND (CarData.TimeSent BETWEEN #summary.Start AND #summary.Stop)

回复收藏 0 原文

这个俗人 2024-12-23 21:16:02

这里有一种方法可以做到这一点，尽管我不喜欢它执行 3 次子查询。这是我希望有人有更好主意的案例之一。

update #summary
  set TopSpeed = 
  (
      select top 1 TopSpeed
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
  ) 
  SpeedTime = 
  (
      select top 1 SpeedTime
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
  ) 
  SpeedDriver = 
  (
      select top 1 SpeedDriver
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
    )

Where here's a way to do it, though I don't like it doing the subquery 3 times. It's one of those cases where I hope someone has a better idea.

update #summary
  set TopSpeed = 
  (
      select top 1 TopSpeed
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
  ) 
  SpeedTime = 
  (
      select top 1 SpeedTime
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
  ) 
  SpeedDriver = 
  (
      select top 1 SpeedDriver
      from CarData
      where CarData.VehicleId = #summary.VehicleId 
      and CarData.TimeSent between #summary.Start and #summary.Stop
      order by CarData.TopSpeed desc, TimeSent desc
    )

回复收藏 0 原文

~没有更多了~