SQL 更新每条记录及其在有序选择中的位置
我通过 OleDb 使用 Access。我有一个包含 ID、GroupID、时间和地点列的表。应用程序将新记录插入表中,遗憾的是位置计算不正确。
我想根据时间升序更新组中的每条记录及其正确位置。
因此,假设以下数据:
ID GroupId Time Place
Chuck 1 10:01 2
Alice 1 09:01 3
Bob 1 09:31 1
应该导致:
ID GroupId Time Place
Chuck 1 10:01 3
Alice 1 09:01 1
Bob 1 09:31 2
我可以使用游标提出一个解决方案,但据我所知,这在 Access 中是不可能的。
I'm using Access via OleDb. I have a table with columns ID, GroupID, Time and Place. An application inserts new records into the table, unfortunately the Place isn't calculated correctly.
I want to update each record in a group with its correct place according to its time ascending.
So assume the following data:
ID GroupId Time Place
Chuck 1 10:01 2
Alice 1 09:01 3
Bob 1 09:31 1
should result in:
ID GroupId Time Place
Chuck 1 10:01 3
Alice 1 09:01 1
Bob 1 09:31 2
I could come up with a solution using a cursor but that's AFAIK not possible in Access.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我刚刚搜索了“Access 排名”,得到了此 support.microsoft 结果 。
您似乎创建了一个带有以下表达式的字段的查询:
我无法测试它,所以我希望它能起作用。
使用这个你也许可以做到(其中
queryAbove
是上面的查询):虽然可能性不大,但请尝试一下。
I just did a search on performing "ranking in Access" and I got this support.microsoft result.
It seems you create a query with a field that has the following expression:
I can't test this, so I hope it works.
Using this you may be able to do (where
queryAbove
is the above query):It's a long shot but please give it a go.
我不认为时间是数字或时间格式的列,不幸的是时间是包含时间格式的数字和数字的文本字符串。这就是为什么在时间列之后排序是非法的。删除分隔符“:”和“,”转换为整数,然后按数字排序可以完成这项工作
I don't think time is a number or time formatted column, time is unfortunately a text string containing the numbers and dilimetrs of the time format. This is why sorting after the time column is illegal. Removing the dilimiters ":" and "," casting to integer and then sorting numirically could do the job