在 WHERE 条件下获取 SQL 中的最后一条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6972286/
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
Getting the last record in SQL in WHERE condition
提问by Dinup Kandel
i have loanTablethat contain two field loan_idand status
我有loanTable包含两个字段loan_id和status
loan_id status
==============
1 0
2 9
1 6
5 3
4 5
1 4 <-- How do I select this??
4 6
In this Situation i need to show the last Statusof loan_id1 i.e is status4. Can please help me in this query.
在这种情况下，我需要显示1 中的最后一个Status，loan_id即status4。请帮助我完成此查询。
回答by Jonathan Leffler
Since the 'last' row for ID 1 is neither the minimum nor the maximum, you are living in a state of mild confusion. Rows in a table have no order. So, you should be providing another column, possibly the date/time when each row is inserted, to provide the sequencing of the data. Another option could be a separate, automatically incremented column which records the sequence in which the rows are inserted. Then the query can be written.
由于 ID 1 的“最后”行既不是最小值也不是最大值，因此您处于一种轻微的混乱状态。表中的行没有顺序。因此，您应该提供另一列，可能是插入每一行的日期/时间，以提供数据的排序。另一种选择可能是一个单独的、自动递增的列，它记录插入行的顺序。然后可以编写查询。
If the extra column is called status_id, then you could write:
如果额外的列被调用status_id，那么你可以写：
SELECT L1.*
FROM LoanTable AS L1
WHERE L1.Status_ID = (SELECT MAX(Status_ID)
FROM LoanTable AS L2
WHERE L2.Loan_ID = 1);
(The table aliases L1 and L2 could be omitted without confusing the DBMS or experienced SQL programmers.)
（表别名 L1 和 L2 可以省略，不会混淆 DBMS 或有经验的 SQL 程序员。）
As it stands, there is no reliable way of knowing which is the last row, so your query is unanswerable.
就目前而言，没有可靠的方法知道哪一行是最后一行，因此您的查询无法回答。
回答by Skorpioh
Does your table happen to have a primary id or a timestamp? If not then what you want is not really possible.
您的表是否碰巧有主 ID 或时间戳？如果没有，那么你想要的东西是不可能的。
If yes then:
如果是，那么：
SELECT TOP 1 status
FROM loanTable
WHERE loan_id = 1
ORDER BY primaryId DESC
-- or
-- ORDER BY yourTimestamp DESC
回答by ain
I assume that with "last status" you mean the record that was inserted most recently? AFAIK there is no way to make such a query unless you add timestamp into your table where you store the date and time when the record was added. RDBMS don't keep any internal order of the records.
我假设“最后状态”是指最近插入的记录？AFAIK 除非您将时间戳添加到您存储添加记录的日期和时间的表中，否则无法进行此类查询。RDBMS 不保留记录的任何内部顺序。
回答by abatishchev
But if last = last inserted, that's not possible for current schema, until a PK addition:
但是，如果 last = last added，则在添加 PK 之前，对于当前架构是不可能的：
select top 1 status, loan_id
from loanTable
where loan_id = 1
order by id desc -- PK
回答by paparazzo
Use a data reader. When it exits the while loop it will be on the last row. As the other posters stated unless you put a sort on the query, the row order could change. Even if there is a clustered index on the table it might not return the rows in that order (without a sort on the clustered index).
使用数据阅读器。当它退出 while 循环时，它将位于最后一行。正如其他海报所述，除非您对查询进行排序，否则行顺序可能会改变。即使表上有聚集索引，它也可能不会按该顺序返回行（没有对聚集索引进行排序）。
SqlDataReader rdr = SQLcmd.ExecuteReader();
while (rdr.Read())
{
}
string lastVal = rdr[0].ToString()
rdr.Close();
You could also use a ROW_NUMBER() but that requires a sort and you cannot use ROW_NUMBER() directly in the Where. But you can fool it by creating a derived table. The rdr solution above is faster.
您也可以使用 ROW_NUMBER() 但这需要排序并且您不能直接在 Where 中使用 ROW_NUMBER()。但是您可以通过创建派生表来欺骗它。上面的rdr解决方案更快。
回答by Gyan
In oracle database this is very simple.
在oracle 数据库中，这很简单。
select * from (select * from loanTable order by rownum desc) where rownum=1
select * from (select * from LoanTable order by rownum desc) where rownum=1
回答by Jabu
Hi if this has not been solved yet. To get the last record for any field from a table the easiest way would be to add an ID to each record say pID. Also say that in your table you would like to hhet the last record for each 'Name', run the simple query
你好，如果这还没有解决。要从表中获取任何字段的最后一条记录，最简单的方法是为每个记录添加一个 ID，比如 pID。还要说，在您的表中，您希望为每个“名称”获取最后一条记录，运行简单查询
SELECT Name, MAX(pID) as LastID
INTO [TableName]
FROM [YourTableName]
GROUP BY [Name]/[Any other field you would like your last records to appear by]
You should now have a table containing the Names in one column and the last available ID for that Name. Now you can use a join to get the other details from your primary table, say this is some price or date then run the following:
您现在应该有一个包含一列中的名称和该名称的最后一个可用 ID 的表。现在您可以使用连接从主表中获取其他详细信息，假设这是某个价格或日期，然后运行以下命令：
SELECT a.*,b.Price/b.date/b.[Whatever other field you want]
FROM [TableName] a LEFT JOIN [YourTableName]
ON a.Name = b.Name and a.LastID = b.pID
This should then give you the last records for each Name, for the first record run the same queries as above just replace the Max by Min above.
然后，这应该为您提供每个名称的最后一条记录，对于第一条记录，运行与上述相同的查询，只需将上面的 Max 替换为 Min。
This should be easy to follow and should run quicker as well
这应该很容易遵循，并且应该运行得更快
回答by Elliott Marshall
If you don't have any identifying columns you could use to get the insert order. You can always do it like this. But it's hacky, and not very pretty.
如果您没有任何标识列，您可以使用它来获取插入顺序。你总是可以这样做。但它很hacky，而且不是很漂亮。
select
t.row1,
t.row2,
ROW_NUMBER() OVER (ORDER BY t.[count]) AS rownum from (
select
tab.row1,
tab.row2,
1 as [count]
from table tab) t
So basically you get the 'natural order' if you can call it that, and add some column with all the same data. This can be used to sort by the 'natural order', giving you an opportunity to place a row number column on the next query.
所以基本上你会得到“自然顺序”，如果你能称之为“自然顺序”，并添加一些包含所有相同数据的列。这可用于按“自然顺序”排序，让您有机会在下一个查询中放置行号列。
Personally, if the system you are using hasn't got a time stamp/identity column, and the current users are using the 'natural order', I would quickly add a column and use this query to create some sort of time stamp/incremental key. Rather than risking having some automation mechanism change the 'natural order', breaking the data needed.
就个人而言，如果您使用的系统没有时间戳/身份列，而当前用户使用的是“自然顺序”，我会快速添加一列并使用此查询来创建某种时间戳/增量钥匙。而不是冒险让一些自动化机制改变“自然顺序”，破坏所需的数据。
回答by vamsi
I think this code may help you:
我认为这段代码可以帮助你：
WITH cte_Loans
AS
(
SELECT LoanID
,[Status]
,ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RN
FROM LoanTable
)
SELECT LoanID
,[Status]
FROM LoanTable L1
WHERE RN = ( SELECT max(RN)
FROM LoanTable L2
WHERE L2.LoanID = L1.LoanID)
