sql - 在字符串列上使用 GROUP BY 时未获得分组结果

Question

我有一个收件箱表，我想在其中返回按电话号码分组的消息(如按发件人分组的 GMail)。并且 sms_content 应该是 id 的最后一条消息。但是当我使用 GROUP BY 关键字时，它不会返回分组结果。

这是我的“收件箱”表。有 2 条来自号码“+123456789”的消息应分组。

+----+--------------+------------------------------+
| id | phone_number |         sms_content          |
+----+--------------+------------------------------+
|  1 | +123456789   | Hello, my name is            |
|  2 | +987654321   | What's up, long time no see! |
|  3 | +123456789   | John, I want to meet you!    |
+----+--------------+------------------------------+

我的第一个查询失败并出现此错误消息

select * from inbox group by phone_number

Error: Error in query: ERROR: column "inbox.id" must appear in the GROUP BY clause or be used in an aggregate function

我尝试的第二个查询没有返回按 phone_number 分组的结果

select *
from inbox
group by phone_number, id, sms_content
order by id desc

结果(未分组):

+----+--------------+------------------------------+
| id | phone_number |         sms_content          |
+----+--------------+------------------------------+
|  3 | +123456789   | John, I want to meet you!    |
|  2 | +987654321   | What's up, long time no see! |
|  1 | +123456789   | Hello, my name is            |
+----+--------------+------------------------------+

我希望看到的结果是这样的。电话号码字段应分组，sms_content 应显示该电话号码的最新 sms_content。

+--------------+------------------------------+
| phone_number |         sms_content          |
+--------------+------------------------------+
| +123456789   | John, I want to meet you!    |
| +987654321   | What's up, long time no see! |
+--------------+------------------------------+

Answer 1

对于这种情况，PostgreSQL 有一个有用的标准扩展:

SELECT DISTINCT ON (phone_number)
       phone_number,
       sms_content
FROM inbox
ORDER BY phone_number, id DESC

Details :

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal.

( Sample fiddle )