SQL Server表列，其中包含条目组的最大值。访问/ SQL /共享点

I have a problem with SQL Server table design. I am making simple location database with app as a project data will be displayed in a Sharepoint site.

我有SQL Server表设计的问题。我正在使用app创建简单的位置数据库，因为项目数据将显示在Sharepoint站点中。

Here are my tables:

这是我的表格：

Phones

手机

-----------------------------------------------
PhoneId | Model | MaxSpeed | DistanceTraveled |
   1    | Lumia | Problem1 |     Problem2     |
   2    | Lumia | Problem1 |     Problem2     |
-----------------------------------------------

Locations

地点

----------------------------------------------------------------------------------
LocationId | PhoneId | Long | Lat | Speed | Direction | DistanceFromLastLocation | 
     1     |    1    | data | dat |   3   |   data    |          Problem3        |         
     2     |    2    | data | dat |   4   |   data    |          Problem3        |
     3     |    2    | data | dat |   6   |   data    |          Problem3        |
     4     |    1    | data | dat |   5   |   data    |          Problem3        |
----------------------------------------------------------------------------------

And my questions are:

我的问题是：

I want MaxSpeed to be max value from all location entries for given phone. I wrote a SQL query for selecting max value but I don't know how to refer to PhoneId. In my query access each time ask me for PhoneId - if you can tell me how to dynamic refer to PhoneId this would be awesome.

我希望MaxSpeed是给定手机的所有位置条目的最大值。我写了一个SQL查询来选择最大值，但我不知道如何引用PhoneId。在我的查询访问每次问我PhoneId - 如果你能告诉我如何动态参考PhoneId这将是很棒的。
Similar problem I have no idea how to refer to PhoneId (for each Phone).

类似的问题我不知道如何参考PhoneId（每个电话）。
This column should be calculated based on value from previous entry (for given Phone).

此列应基于先前条目（对于给定电话）的值计算。

Is this even possible in SQL or should I use macros?

这在SQL中是否可行，或者我应该使用宏？

My knowledge of SQL is limited but if you guys can point in right direction I would be thankful.

我对SQL的了解是有限的，但如果你们能指出正确的方向，我会感激不尽。

1 个解决方案

#1

For problems #1 and #2, use something like -

对于问题＃1和＃2，使用类似的东西 -

with [Maximums] (PhoneID, MaxSpeed, MaxDistanceTraveled)
(
    select
        PhoneID
        max(Speed) as [MaxSpeed],
        max(DistanceTraveled) as [MaxDistanceTraveled]
    from
        Locations
    group by
        PhoneID
    order by
        PhoneID asc
)
update
    Phones
set
    MaxSpeed = m.MaxSpeed,
    DistanceTraveled = m.MaxDistanceTraveled
from
    Phones as ph
        inner join
    Maximums as m
        on (ph.PhoneID = m.PhoneID)

ALERT - I did not test this so view it as a code template - Let me know if you have problems you can't resolve and post any error messages.

警告 - 我没有对此进行测试，因此将其视为代码模板 - 如果您遇到问题，请告诉我您无法解决并发布任何错误消息。

For problem #3 you need to provide more detail. What exactly do you mean by "previous entry"?

对于问题＃3，您需要提供更多详细信息。你以前的“上一次参赛”究竟是什么意思？

#1