需要有关SQL查询的帮助，该查询返回符合特定条件的持续记录 [英] Need help with a SQL query that returns the lasts records matching a certain criteria

问题描述

我有下表：

CREATE TABLE "posting" (
    "id" integer NOT NULL PRIMARY KEY,
    "amount" real NOT NULL,
    "balance" real NOT NULL,
    "account_id" integer NOT NULL REFERENCES "account" ("id"),
    "asset_type_id" integer NOT NULL REFERENCES "asset_type" ("id")        
)

，我以没有空格的方式手动生成ID（无法删除记录）。确保以下语句将返回最新记录：

For this table, I manually generate ids in a way that there's no gaps(records can't be deleted). It is guaranteed that the following statement will return the latest record :

SELECT * FROM posting ORDER BY id DESC LIMIT 1

问题是，现在我不仅需要检索 account_id每个组合的最后一条记录，而且还需要检索最后一条记录和 asset_type_id。例如，假设我有两个帐户和两个 asset_types（分别具有ID 1和2）以及以下记录（省略金额和余额）：

The problem is that now I need to retrieve not only the last, but the last record for each combination of 'account_id' and 'asset_type_id'. For example, lets say I have two 'accounts' and two 'asset_types' (both with ids 1 and 2) and the following records(omiting amount and balance):

id | account_id | asset_type_id 
1  |    1       |    1
2  |    2       |    1
3  |    1       |    2
4  |    2       |    1
5  |    2       |    2
6  |    2       |    2

由于记录5和2被替换，它将返回记录6、4、3和1分别为6和4。我不知道如何用SQL表示它，不胜感激。

It will return me records 6, 4, 3 and 1 since records 5 and 2 were "replaced" by 6 and 4 respectively. I have no idea how to represent this in SQL, any help is appreciated.

推荐答案

您需要窥视的是最大的价值account_id和asset_type_id组的ID的数量

What You need to peek is the largest value of ID for group of account_id and asset_type_id

SELECT MAX（ID），account_id，asset_type_id从发布组按account_id，asset_type_id进行分组；

SELECT MAX(ID),account_id,asset_type_id FROM posting group by account_id, asset_type_id;

表

id | account_id | asset_type_id 
1  |    1       |    1
2  |    2       |    1
3  |    1       |    2
4  |    2       |    1
5  |    2       |    2
6  |    2       |    2

分组后

id | account_id | asset_type_id 
1   |    1       |    1
2,4 |    2       |    1
3   |    1       |    2
5,6 |    2       |    2

在Max之后

id | account_id | asset_type_id 
1  |    1       |    1
3  |    1       |    2
4  |    2       |    1
6  |    2       |    2

编辑：

问题2：如何在不使用整个表的情况下运行此查询？

Q2:How To run this query without using whole table ?

SQL select语句具有某些阶段，这些阶段以特定顺序完成，每个阶段都为下一阶段创建一个临时输入表。

The SQL select statement has some phases that are done in specific order each of this phase create a temporary input table for next phase.

(5) SELECT (5-2) DISTINCT (5-3) TOP (<top specification>) 
(5-1) <select clauses>
(1) FROM (1-J) <left table> <connection type> JOIN <right table> ON <predicates of on clause>
<alias>
(2) WHERE <predicates of where clause>
(3) GROUP BY <grouping specification>
(4) HAVING <predicates of having clause>
(6) ORDER BY <list specifying the order>

如您所见，第一步是FROM，在您的情况下是一个表，那么我们去吧到哪里，所以当您在此处放置一些谓词时，例如 account_id在1到10之间，而asset_type_id在1到10 上时，您只会在步骤3中将该表作为组进行操作通过。

As You see the first step is the FROM, that in your case is one table, then we go to WHERE so when You put some predicates here for example account_id between 1 and 10 AND asset_type_id between 1 and 10 you will only operate on this piece of table in step 3 that is group by.

这篇关于需要有关SQL查询的帮助，该查询返回符合特定条件的持续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！