情况
是这样的：
在我的数据库中，我有一个包含线程的表和一个包含回复的表。两者都有一个时间戳字段。
现在我正在开发一个论坛，并希望按以下方式对线程进行排序：
- 如果线程有回复，则：ORDER BY tblReply.Timestamp DESC
- 否则，线程没有回复：ORDER BY tblThread.Timestamp DESC
我不知道如何将这两个组合在一个语句中。
我现在的查询：
SELECT `PK_ThreadID`, `Title`, `tblUsers`.`Username`, `tblThread`.`Date`, count(tblReply.FK_ThreadID) AS number_replies FROM (`tblThread`)
JOIN `tblUsers` ON `tblUsers`.`PK_UserID` = `tblThread`.`FK_UserID`
LEFT JOIN `tblReply` ON `tblReply`.`FK_ThreadID` = `tblThread`.`PK_ThreadID`
WHERE `isExpertQuestion` = 0 AND `isPublic` = 1
GROUP BY `PK_ThreadID`
ORDER BY max(tblReply.Date)` desc
//Here it only orders by reply date, so threads with no replies appear at the bottom
如何在此查询中实现所需的排序？
大概是这样的：
SELECT `PK_ThreadID`, `Title`,
`tblUsers`.`Username`,
`tblThread`.`Date`,
count(tblReply.FK_ThreadID) AS number_replies
FROM (`tblThread`)
JOIN `tblUsers` ON `tblUsers`.`PK_UserID` = `tblThread`.`FK_UserID`
LEFT JOIN `tblReply` ON `tblReply`.`FK_ThreadID` = `tblThread`.`PK_ThreadID`
WHERE `isExpertQuestion` = 0 AND `isPublic` = 1
GROUP BY `PK_ThreadID`
ORDER BY
CASE WHEN COUNT(tblReply.FK_ThreadID) > 0 THEN tblReply.Timestamp
WHEN COUNT(tblReply.FK_ThreadID) = 0 OR tblReply.FK_ThreadID IS NULL
THEN tblThread.Timestamp
END DESC