从 SQL 数据库检索最后一行记录时出现问题

发布于 2024-12-01 23:26:26 字数 320 浏览 3 评论 0原文

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(4

戴着白色围巾的女孩 2024-12-08 23:26:27

如果 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).

眼藏柔 2024-12-08 23:26:27

字符串的排序顺序为 R1 -> R10-> R2。你期待什么?

如果您想要数字排序,那么您将需要这个

SELECT TOP 1 RoomID
FROM RoomQuantity
ORDER BY
      CAST(REPLACE(RoomID, 'R', '') AS int) DESC

如果您提供了很多行,则不要指望出色的性能,因为 RoomID 上的索引将被忽略

其他选项:

  • 填充数字(根据 Ralph Shillington 的回答)
  • 将数字存储为int 并在显示时添加“R”(或作为计算列)
  • 使用我的 CAST/REPLACE 作为表中的计算索引列

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

SELECT TOP 1 RoomID
FROM RoomQuantity
ORDER BY
      CAST(REPLACE(RoomID, 'R', '') AS int) DESC

Don't expect great performance if you gave a lot of rows because an index on RoomID will be ignored

Other options:

  • Pad numbers (as per Ralph Shillington's answer)
  • Store the number as int and add the 'R' at display time (or as computed column)
  • Use my CAST/REPLACE as a computed, indexed column in the table
旧话新听 2024-12-08 23:26:27

试试这个

select RoomID  from RoomQuantity order by RoomID  desc limit 1;

try this

select RoomID  from RoomQuantity order by RoomID  desc limit 1;
叶落知秋 2024-12-08 23:26:27

如果您希望检索像数字一样的字符,则必须对数据进行零填充。
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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文