Question

我的 m和我的 s不像我的 rub,因此我希望你能够帮助我。

我的表象是这样:

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 "

It s a listening of active players and I want to check weather they have some unread messages. Now with that string I can do and a sprintf with this :

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 )

NOW 我只有两个问题:

The sql returns more then 1 entry for every field entry to, I would like to return exactly one message for every to (LIMIT 1 by to?) and it has to be the newest (first by 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"

I would like to update the field shown within the same SQL call to NOW() for the retrieved entries.

Answer 1

Edit -

您可以使用这一方法:

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为最低值,然后按[到]分类。否则,你就可以使用最高标准。

这将产生——

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

并且,你可以使用同样的询问方法,获得[own]一栏必须经过目前时间更新的婴儿——

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
)

Answer 2

Regarding point #1 You have no field "uniqueid" in your table. I assume you mean "id" (your primary key)? If this is the case you will need to either update the where clause (changes uniqueid to id) or simply do nothing other than edit your post so we know that there is an additional field.

让我们假设,你有一个叫id的领域,而独一无二的是一个打字体(也用你的扼杀取代百分比)。

select count(*), id 
from messages 
where shown is null and (%s) 
group by id

将向您提供每份假消息。

关于点第2号:你可以在同一询问中进行更新和选择。你们将需要发出两条单独的电话。

update messages 
set shown = Now() 
where [some where clause here]

友情链接