MySQL SQL - 如何找到列中的最高数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1547125/
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
SQL - How to find the highest number in a column?
提问by TheFlash
Let's say I have the following data in the Customers table: (nothing more)
假设我在客户表中有以下数据：（仅此而已）
ID FirstName LastName
-------------------------------
20 John Mackenzie
21 Ted Green
22 Marcy Nate
What sort of SELECTstatement can get me the number 22, in the ID column?
什么样的SELECT语句可以让我得到 ID 列中的数字 22？
I need to do something like this to generate a unique ID. Sure I can let the system do this via auto-increment, but then how would I get the auto generated ID?
我需要做这样的事情来生成一个唯一的 ID。当然我可以让系统通过自动增量来做到这一点，但是我将如何获得自动生成的 ID？
I thought of SELECT ID FROM Customersand counting the rows returned but this seems horribly inefficient, and in this case, it will incorrectly return "3", though I need a unique ID of 23.
我想到SELECT ID FROM Customers并计算返回的行数，但这似乎效率极低，在这种情况下，它会错误地返回“3”，尽管我需要一个唯一的 ID 23。
回答by notnoop
You can do
你可以做
SELECT MAX(ID) FROM Customers;
回答by David Andres
If you've just inserted a record into the Customers table and you need the value of the recently populated ID field, you can use the SCOPE_IDENTITYfunction. This is only useful when the INSERThas occurred within the same scope as the call to SCOPE_IDENTITY.
如果您刚刚在客户表中插入了一条记录，并且需要最近填充的 ID 字段的值，则可以使用该SCOPE_IDENTITY函数。这仅在INSERT发生在与调用相同的范围内时才有用SCOPE_IDENTITY。
INSERT INTO Customers(ID, FirstName, LastName)
Values
(23, 'Bob', 'Smith')
SET @mostRecentId = SCOPE_IDENTITY()
This may or may not be useful for you, but it's a good technique to be aware of. It will also work with auto-generated columns.
这可能对您有用也可能没有用，但这是一个需要注意的好技巧。它也适用于自动生成的列。
回答by Joel Enanod Jr
SELECT * FROM Customers ORDER BY ID DESC LIMIT 1
Then get the ID.
然后拿到身。
回答by Brisbe
To get it at any time, you can do SELECT MAX(Id) FROM Customers.
要随时获取，您可以这样做SELECT MAX(Id) FROM Customers。
In the procedure you add it in, however, you can also make use of SCOPE_IDENTITY-- to get the id last added by that procedure.
This is safer, because it will guarantee you get your Id--just in case others are being added to the database at the same time.
但是，在您添加它的过程中，您也可以使用SCOPE_IDENTITY-- 来获取该过程最后添加的 id。
这更安全，因为它可以保证您获得您Id的 -- 以防同时将其他人添加到数据库中。
回答by deostroll
select max(id) from customers
回答by Damon
If you're talking MS SQL, here's the most efficient way. This retrieves the current identity seed from a table based on whatever column is the identity.
如果您说的是 MS SQL，这是最有效的方法。这会根据标识的任何列从表中检索当前标识种子。
select IDENT_CURRENT('TableName') as LastIdentity
Using MAX(id)is more generic, but for example I have an table with 400 million rows that takes 2 minutes to get the MAX(id). IDENT_CURRENTis nearly instantaneous...
使用MAX(id)更通用，但例如我有一个包含 4 亿行的表，需要 2 分钟才能获取MAX(id). IDENT_CURRENT几乎是瞬间...
回答by monksy
select max(id) from Customers
回答by Steven Graham
If you are using AUTOINCREMENT, use:
如果您正在使用AUTOINCREMENT，请使用：
SELECT LAST\_INSERT\_ID();
Assumming that you are using Mysql: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
假设您使用的是 Mysql：http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Postgres handles this similarly via the currval(sequence_name)function.
Postgres 通过currval(sequence_name)函数类似地处理这个。
Note that using MAX(ID)is not safe, unless you lock the table, since it's possible (in a simplified case) to have another insert that occurs before you call MAX(ID)and you lose the id of the first insert. The functions above are session based so if another session inserts you still get the ID that you inserted.
请注意，使用MAX(ID)是不安全的，除非您锁定表，因为有可能（在简化的情况下）在您调用之前发生另一个插入MAX(ID)并且您丢失了第一个插入的 id。上面的函数是基于会话的，所以如果另一个会话插入，您仍然会得到您插入的 ID。
回答by Arnab Das
You can also use relational algebra. A bit lengthy procedure, but here it is just to understand how MAX() works:
您还可以使用关系代数。有点冗长的过程，但这里只是为了了解 MAX() 是如何工作的：
E := πID(Table_Name)
E1 := πID(σID >= ID'((ρID'E) ? E)) – πID(σID < ID'((ρID'E) ? E))
E := π ID(Table_Name)
E1 := π ID(σ ID >= ID'((ρ ID'E) ? E)) – π ID(σ ID < ID'((ρ ID'E) ? E) )
Your answer: Table_Name ? E1
你的答案是： Table_Name ？E1
Basically what you do is subtract set of ordered relation(a,b) in which a<b from A where a, b ∈ A.
基本上你所做的是<从 A 中减去一组有序关系（a，b），其中 a b ，其中 a，b ∈ A。
For relation algebra symbols see: Relational algebra From Wikipedia
有关关系代数符号，请参阅： 来自维基百科的关系代数
回答by paxdiablo
If you're not using auto-incrementing fields, you can achieve a similar result with something like the following:
如果您不使用自动递增字段，则可以使用以下内容获得类似的结果：
insert into Customers (ID, FirstName, LastName)
select max(ID)+1, 'Barack', 'Obama' from Customers;
This will ensure there's no chance of a race condition which could be caused by someone else inserting into the table between your extraction of the maximum ID and your insertion of the new record.
这将确保不会出现因其他人在您提取最大 ID 和插入新记录之间插入表而导致的竞争条件。
This is using standard SQL, there are no doubt better ways to achieve it with specific DBMS' but they're not necessarily portable (something we take very seriously in our shop).
这是使用标准 SQL，毫无疑问，使用特定的 DBMS 有更好的方法来实现它，但它们不一定是可移植的（我们在商店中非常重视这一点）。
