我有这两个表,我想按第二个表的行数排序...我想要一个查询向我显示每个postid的 postid 和 count() 评论......上面的查询我工作得很完美但是我想添加查询和表格帖子只是我需要帖子表来选择* ...用于另一个工作...但它必须在同一个查询...我的问题是当我添加和posts表到上面的查询它将 count() 乘以8,即在帖子表中有多少字段......它可以完成他的工作并且像这样但是我不喜欢这样或不正确,或者可能有另一种方式更简单或更正确!提前致谢!
comments posts
+---------+------------+ +---------+-----------------+
| Name | Type | | Name | Type |
+---------+------------+ +---------+-----------------+
| id |int(10) prim| +----| postid | int(10) prim |
| name |varchar(128)| | | title | varchar(100) |
| email |varchar(255)| | | image | varchar(150) |
| body | text | | | video | varchar(200) |
| postid | int(10) |--+ | body | text |
| dt | timestamp | | author | varchar(50) |
+---------+------------+ | postdate| timestamp |
| category|enum('1','2','3')|
+---------+-----------------+
<?php
$sql = "SELECT comments.postid, count(*) FROM comments GROUP BY comments.postid ORDER BY count(*) DESC LIMIT 3";
$query = mysqli_query($db_conx, $sql);
?>
Output of that query:
+--------+----------+
| postid | count(*) |
+--------+----------+
| 14 | 6 |
| 13 | 4 |
| 15 | 3 |
+--------+----------+
<?php
$sql = "SELECT comments.postid, count(*) FROM posts, comments GROUP BY comments.postid ORDER BY count(*) DESC LIMIT 3";
$query = mysqli_query($db_conx, $sql);
?>
Output of that query:
+--------+----------+
| postid | count(*) |
+--------+----------+
| 14 | 48 |
| 13 | 32 |
| 15 | 24 |
+--------+----------+