問(wèn)題描述

我有 loanTable 包含兩個(gè)字段 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 的最后一個(gè) Status，即 status 4.請(qǐng)幫助我進(jìn)行此查詢.

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 的最后"行既不是最小值也不是最大值，因此您處于一種輕微的混亂狀態(tài).表中的行沒(méi)有順序.因此，您應(yīng)該提供另一列，可能是插入每一行的日期/時(shí)間，以提供數(shù)據(jù)的排序.另一種選擇可能是一個(gè)單獨(dú)的、自動(dòng)遞增的列，它記錄插入行的順序.然后查詢就可以寫(xiě)了.

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，那么你可以寫(xiě):

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 可以省略，不會(huì)混淆 DBMS 或有經(jīng)驗(yàn)的 SQL 程序員.)

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

就目前而言，沒(méi)有可靠的方法知道哪一行是最后一行，因此您的查詢無(wú)法回答.

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

這篇關(guān)于在 WHERE 條件下獲取 SQL 中的最后一條記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！