我有一个名为"messages":
id, owner, content
"id1", "bob", "c1"
"id1", "alice", "c2"
"id2", "bob", "c3"
"id3", "charles", "c4"
消息表示有序事件的序列。如果有两个事件具有相同的id,则只显示最后一个事件。
(通过"last"我的意思是,从上到下,我不知道我是否可以使用row来排序或者我是否需要一个自动递增的整数)
我想检索id,所有者,内容,但只接收不同的id:
"id1","alice","c2"
"id2","bob","c3"
"id3","charles","c4"
如何用sqlite查询这个?
可以在将content
列转换为整数值时使用ROW_NUMBER()
窗口函数,例如
WITH t1 AS
(
SELECT *,
ROW_NUMBER() OVER
(PARTITION BY id ORDER BY CAST(REPLACE(content,'c','') AS INT) DESC) AS rn
FROM t
)
SELECT id, owner, content
FROM t1
WHERE rn = 1
假设顺序是基于该列的数值,该列只有这些整数的c
前缀值。