我的代码是这样的,
$sql = "SELECT Month(time) as Month, Year(time) as Year,
title, COUNT(*) AS total FROM posts GROUP BY Year, Month ORDER BY time DESC";
$stmt = $conn->query($sql);
if ($stmt->num_rows > 0) {
while($row = $stmt->fetch_array()){
echo "<div class=title>" . $row["title"]. "</div>";
}
}
它应该输出4个标题,
Bellavisa
Mist Neting
Turkey is cool!
Cock of the Rock
但它只输出
Bellavisa
Turkey is cool!
Cock of the Rock
请注意,bellavisa和mist-neting是在同一年和同一个月,(设置一个档案列表)
编辑
以下是的一些表格数据
title "bellavisa" content "yadadada" time "timestamp ..." Author "author"
title "mist nesting" content "yadadada" time "timestamp ..." Author "author"
好吧,title
是每个帖子的,所以要获得所有标题,你不应该使用GROUP BY
,而COUNT(*)
是每个月的,所以为了获得计数,你需要按照你的方式使用GROUP BY
,所以在一个简单的SELECT
中,你可以选择一个或另一个,但不能同时选择两者。
要同时选择两者,您需要使用子查询,类似
$sql = "SELECT Month(time) as Month, Year(time) as Year, title, (SELECT COUNT(*) FROM posts WHERE Month(time) = Month AND Year(time) = Year) AS total FROM posts ORDER BY time DESC;";
实际上,查询会选择所有帖子,并为每个帖子计算一个计数。这不是最有效的方法,如果每个帖子都有一个唯一的ID,你可以使用联接来重写它。但对于一个大小合理的表,这个查询会很好地工作。