按子项分组，但如果是新读取，如果不是 1，则显示 0

Question

好吧，我知道这听起来很愚蠢。但我已经尝试了一切。

这是我的代码，

SELECT toD.username AS ToUser,
fromD.username AS FromUser, 
rvw.* FROM usermessages AS rvw 

LEFT JOIN users AS toD 
ON toD.id = rvw.touserid 

LEFT JOIN users AS fromD ON fromD.id = rvw.fromuserid 

WHERE touserid = '" . $this->userid . "'
AND deleted = '0' 

GROUP BY subkey 

ORDER BY rvw.read ASC, rvw.created DESC

虽然这确实有效，但我发现如果有新消息，并且读取为 0，它仍然显示为 1。我知道这是因为我将行分组在一起。

但我不确定是否还有其他方法可以做到这一点。

Answer 1

它不起作用，因为无论您如何尝试对集合进行排序，mysql 都可以从组中返回任何行。要使用某种自定义顺序查找组中的第一行，您必须将其分为两个任务 - 首先查找分组所依据的列的所有不同值，然后查找子查询中每个引用值的第一行。因此，您的查询应如下所示：

SELECT toD.username AS ToUser, fromD.username as FromUser, msg.* FROM 
       ( SELECT DISTINCT touserid, subkey
       FROM usermessages
       WHERE touserid = 'insert_your_id_here'
       AND deleted=0 ) msgg
JOIN usermessages msg
ON msg.id = ( SELECT msgu.id
              FROM usermessages msgu
              WHERE msgu.touserid = msgg.touserid
              AND msgu.subkey = msgg.subkey
              AND deleted=0
              ORDER BY msgu.read ASC, msgu.created DESC
              LIMIT 1 )
JOIN users fromD ON msg.fromuserid = fromD.id
JOIN users toD ON msg.touserid = toD.id

确保您在 (touserid,subkey) 上有索引。根据您的数据库有多大，您可能需要更多。