在 WHERE 条件下获取 SQL 中的最后一条记录 [英] Getting the last record in SQL in WHERE condition

问题描述

我有 loanTable 包含两个字段 loan_id 和 status

i have loanTable that contain two field loan_id and status

loan_id status
==============
1       0
2       9
1       6
5       3
4       5
1       4  <-- How do I select this??
4       6

在这种情况下，我需要显示 loan_id 1 的最后一个 Status，即 status 4.请帮助我进行此查询.

In this Situation i need to show the last Status of loan_id 1 i.e is status 4. Can please help me in this query.

推荐答案

由于 ID 1 的最后"行既不是最小值也不是最大值，因此您处于一种轻微的混乱状态.表中的行没有顺序.因此，您应该提供另一列，可能是插入每一行的日期/时间，以提供数据的排序.另一种选择可能是一个单独的、自动递增的列，它记录插入行的顺序.然后查询就可以写了.

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.

如果额外的列被称为status_id，那么你可以写:

If the extra column is called status_id, then you could write:

SELECT L1.*
  FROM LoanTable AS L1
 WHERE L1.Status_ID = (SELECT MAX(Status_ID)
                         FROM LoanTable AS L2
                        WHERE L2.Loan_ID = 1);

(表别名 L1 和 L2 可以省略，不会混淆 DBMS 或有经验的 SQL 程序员.)

(The table aliases L1 and L2 could be omitted without confusing the DBMS or experienced SQL programmers.)

就目前而言，没有可靠的方法知道哪一行是最后一行，因此您的查询无法回答.

As it stands, there is no reliable way of knowing which is the last row, so your query is unanswerable.

这篇关于在 WHERE 条件下获取 SQL 中的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！