MySQL 困难查询

Question

我删除了我之前的问题以使其更容易理解。我正在使用此数据库架构开发一个论坛:

ID:INT->PRIMARY-KEY->AUTO_INCREMENT
IDTOPIC:INT (0 if it is the "father" topic OR father's ID if it's a reply)
IDUSER:INT (ID of user who posted)
CONTENT:MEDIUMTEXT
DATE:TIMESTAMP

我需要进行按日期排序的查询，如果没有回复，只在何处获取最后一个线程回复(如果“我是最后一个回复的用户”，IDUSER<>$userid 则不是)或父线程主题。即使是主题或回复结果，我也需要打印第一个线程 IDTOPIC/IDUSER

我理想的结果应该是这样的:

ID:IDTOPIC:IDUSER:CONTENT:DATE:IDLASTREPLY:IDLASTUSERREPLY:LASTCONTENTREPLY:LASTDATEREPLY

如果最后四个字段为NULL 则没有问题。我需要最快的查询。请帮助我!

Answer 1

SELECT fa.ID, fa.IDTOPIC, fa.IDUSER, fa.CONTENT, fa.DATE,
  re.ID as IDLASTREPLY,
  re.IDUSER as IDLASTUSERREPLY,
  re.CONTENT as LASTCONTENTREPLY,
  re.DATE as LASTDATEREPLY,
  CASE WHEN (re.DATE is NULL) THEN fa.DATE ELSE re.DATE END as LASTUPDATE
FROM post fa
  LEFT JOIN post re ON re.ID =
    (SELECT ID FROM post WHERE IDTOPIC = fa.ID ORDER BY DATE DESC LIMIT 1)
WHERE fa.IDTOPIC = 0
ORDER BY LASTUPDATE DESC

随心所欲地订购。
但这不是很好的表现。我认为您可以改进表结构。

编辑:为 ORDER 添加 LASTUPDATE