sql - 关于可能嵌套的 sql 查询的帮助

Question

我正在处理 sql，我的 sql 不如我的 ruby​​，所以我希望你能帮助我。

我有一个看起来像这样的表:

messages:
id INTEGER PRIMARY KEY AUTO_INCREMENT
to VARCHAR(255)
text text
shown DATETIME

目前我的脚本根据在线玩家的数量生成这部分。

"to = 'STEAM_0:0:xxx' OR to = 'STEAM_0:0:xxy' OR to = 'STEAM_0:0:xxz'"

这是对活跃玩家的倾听，我想查看他们是否有未读消息。现在有了那个字符串，我可以用这个做一个 sprintf :

SELECT * FROM messages WHERE shown IS NULL AND (%s)"

并得到一个漂亮的格式化字符串:

SELECT * FROM messages WHERE shown IS NULL AND (to = 'STEAM_0:0:xxx' OR to = 'STEAM_0:0:xxy' OR to = 'STEAM_0:0:xxz')

现在我只有两个问题:

1. SQL 为每个字段条目 to 返回超过 1 个条目，我想为每个 to 返回一条消息(LIMIT 1 by to?) 并且它必须是最新的(首先是 id)。

为了更清楚地说明这一点，假设我有一个这样的表:

id, to,  text
1,  "x", "text1"
2,  "x", "text2"
3,  "y", "text3"
4,  "z", "text4"
5,  "y", "text5"
6,  "z", "text6"
7,  "y", "text7"

我想得到以下信息:

1,  "x", "text1"
3,  "y", "text3"
4,  "z", "text4"

1. 我想在同一 SQL 调用中更新字段 shown 以获取检索到的条目的 NOW()。

Answer 1

编辑 -

您可以使用此查询 -

Select T1.[id],T1.[to],T1.[text] from Messages T1
inner join
   ( Select [to], min([id]) as id from Messages group by [to] ) T2
on T1.[id] = T2.id 

我假设最新的 id 作为 id 的最小值，同时按 [to] 分组。否则，您可以使用 max([id])

这会产生 -

1,  "x", "text1"
3,  "y", "text3"
4,  "z", "text4"

并且，您可以使用相同的查询来获取必须使用当前日期时间更新的 [shown] 列的 ID -

UPDATE messages SET shown = Now() 
where [id] in 
( 
   Select T1.[id] from messages T1 
   inner join 
   ( Select [to], min([id]) as id from messages group by [to]) T2
   on T1.[id] = T2.id
)