在 sql 查询中是否有诸如 SELECT LAST 之类的东西？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/160304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there any thing such as SELECT LAST in sql query?
提问by jbcedge
I am using sybase database to query the daily transaction report. I had subquery within my script.
我正在使用 sybase 数据库来查询每日交易报告。我的脚本中有子查询。
Here as it goes:
事情是这样的：
SELECT orders.accountid ,items.x,etc
(SELECT charges.mistotal FROM charges where items.id = charges.id)
FROM items,orders
WHERE date = '2008-10-02'
Here I am getting the error message as:
在这里，我收到错误消息：
Subquery cannot return more than one values
子查询不能返回多个值
My values are 7.50, 25.00
我的值是 7.50, 25.00
I want to return the 25.00, but when I use
我想返回 25.00，但是当我使用
(SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id)
My result is 7.50 but I want to return 25.00
我的结果是 7.50 但我想返回 25.00
Does anyone has any better suggestion?
有人有更好的建议吗？
回答by senfo
SELECT TOP 1 *
FROM dbo.YourTable
ORDER BY Col DESC
In your case, I guess that would be
在你的情况下，我想那是
SELECT TOP 1 charges.mistotal
FROM charges where items.id = charges.id
ORDER BY charges.mistotal DESC
回答by Manuel Ferreria
Under what criteria you choose to select the 25.00 instead of the 7.5?
您根据什么标准选择选择 25.00 而不是 7.5？
If its related to the maximum value, you can try using the MAX()function on that field.
如果它与最大值有关，您可以尝试在该字段上使用MAX()函数。
If its related to the chronologically last row added, try using the MAX()on the datetime field, if you have details on the hours and minutes it was added.
如果它与按时间顺序添加的最后一行相关，请尝试在日期时间字段上使用MAX()，如果您有关于添加的小时和分钟的详细信息。
回答by Adam Pierce
You could try this:
你可以试试这个：
SELECT MAX(charges.mistotal) FROM charges WHERE items.id = charges.id
回答by Jonathan Leffler
So, can you use inverse order:
那么，您可以使用逆序吗：
(SELECT TOP 1 charges.mistotal
FROM charges
WHERE items.id = charges.id
ORDER BY charges.mistotal DESC
)
Actually, since you didn't give an explicit order, the sequence of the returned results is undefined, and you are just lucky that it gave you the answer you didn't want; it could have given you the answer you wanted, and then you might not have noticed that it was not always correct until after it went into production.
实际上，由于您没有给出明确的顺序，因此返回结果的顺序是不确定的，而且您很幸运，它给了您不想要的答案；它可以给你你想要的答案，然后你可能没有注意到它在投入生产之前并不总是正确的。
Or, can you use:
或者，您可以使用：
(SELECT MAX(charges.mistotal)
FROM charges
WHERE charges.id = items.id
)
Or did you really want a SUM?
或者你真的想要一个 SUM？
回答by Zote
To get first you use select top 1 | first * from table order ascendingto get last, just invert your order.
要获得第一名，请使用 select top 1 | 第一个 * 从表顺序升序到最后，只需反转您的顺序。
回答by CodeRedick
SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id ORDER BY charges.id DESC
SELECT TOP 1 Charges.mistotal FROM Charges where items.id = Charges.id ORDER BY charge.id DESC
The order by clause will make sure it comes back in the order of the id, and the DESC means descending so it will give you the largest (newest) value first. TOP 1 of course makes sure you just get that one.
order by 子句将确保它按照 id 的顺序返回，而 DESC 表示降序，因此它将首先为您提供最大（最新）的值。TOP 1 当然可以确保你得到那个。
回答by Matt
Sort your subquery. If you want the "last" value, you need to define how you determine which item comes last (remember, SQL result sets are unordered by default).
对子查询进行排序。如果您想要“最后一个”值，则需要定义如何确定最后一个项目（请记住，SQL 结果集默认情况下是无序的）。
For example:
例如：
(SELECT TOP 1 charges.mistotal FROM charges where items.id = charges.id
ORDER BY charges.mistotal DESC)
This would return 25.00 instead of 7.50 (from your data examples above), but I'm assuming that you want this value to be "last" because it's bigger. There may be some other field that it makes more sense for you to sort on; maybe you have a timestamp column, for example, and you could sort on that to get the most recent value instead of the largest value. The key is just defining what you mean by "last".
这将返回 25.00 而不是 7.50（来自上面的数据示例），但我假设您希望此值是“最后一个”，因为它更大。可能还有一些其他领域对您来说更有意义；例如，您可能有一个时间戳列，您可以对其进行排序以获取最新值而不是最大值。关键只是定义你所说的“最后”是什么意思。
