SELECT *
FROM Post_index i LEFT OUTER JOIN
(SELECT Index_Id, CASE WHEN COUNT(*) IS NULL THEN '0' ELSE COUNT(*)
END AS num
FROM [Post_Reply]
GROUP BY Index_Id) R ON i.ID = R.Index_Id
ORDER BY i.ID DESC
一直是这么写的,可sql语句一执行就发现原来是错的。找了找原因,原来是因为LEFT JOIN的原因,右边的数据记录更本就不存在,改成:
SELECT i.*, ISNULL(R.num, 0) AS Expr1
FROM Post_index i LEFT OUTER JOIN
(SELECT Index_Id, COUNT(*) AS num
FROM [Post_Reply]
GROUP BY Index_Id) R ON i.ID = R.Index_Id
ORDER BY i.ID DESC