从 SQL 数据库检索最后一行记录时出现问题
cmd.CommandText = "SELECT RoomID FROM RoomQuantity A WHERE A.RoomID = (SELECT MAX(RoomID) FROM RoomQuantity)";
cmd.CommandText = "SELECT TOP 1 RoomID FROM RoomQuantity ORDER BY RoomID desc;";
数据库 表:房间数量 - 列:房间ID - RoomID 中的数据: R1 R2 R3 R4 R5 R6 R7 R8 R9 R10
问题 - 始终检索 R9 而不是 R10
cmd.CommandText = "SELECT RoomID FROM RoomQuantity A WHERE A.RoomID = (SELECT MAX(RoomID) FROM RoomQuantity)";
cmd.CommandText = "SELECT TOP 1 RoomID FROM RoomQuantity ORDER BY RoomID desc;";
Database
Table : RoomQuantity
- Column : RoomID
- Data in RoomID :
R1
R2
R3
R4
R5
R6
R7
R8
R9
R10
Problem - Always retrieved R9 instead of R10
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
如果 RoomID 不是数字数据类型(正如我所假设的,因为数据是“R1”),则使用字母数字排序。
R9> R1
和
R9> R10
仅比较字符串的开头(此处:2 个字符)。
If RoomID is not a numeric data type (as I would assume, as the data is "R1"), then an alphanumeric sort is used.
R9 > R1
and
R9 > R10
as only the beginning of the strings is compared (here: 2 characters).
字符串的排序顺序为 R1 -> R10-> R2。你期待什么?
如果您想要数字排序,那么您将需要这个
如果您提供了很多行,则不要指望出色的性能,因为 RoomID 上的索引将被忽略
其他选项:
The sort order of a string would be R1 -> R10 -> R2. What did you expect?
If you want a number sort, then you'll need this
Don't expect great performance if you gave a lot of rows because an index on RoomID will be ignored
Other options:
试试这个
try this
如果您希望检索像数字一样的字符,则必须对数据进行零填充。
R01 R02 R03 R04 R05 ... R10 将为您提供您正在寻找的结果。然而,既然您已经走上了这条路,您可能会得出结论,应该重新考虑您的数据模型。
YOu'll have to zero pad your data if you're hoping to retrieve characters likes their are numbers.
R01 R02 R03 R04 R05 ... R10 would give you the result you're looking for. However now that you're down that road, you may conclude that your data model should be rethought.