Mysql> 如何使用单个查询连接 2 个表的数据？(选择 * 并计数)

Question

我的样本数据表的文章:| id (string)| Author (int)| desc (string)|| --- | --- |---|你好| bak| 113| world|

表"rating_info"| post_id(string)| rating (int)|| --- | --- |[au:[au:] [au:[au:[au:] [au:| bac | 114|

我的最终目标:

以某种方式组合两个表，从Posts中选择所有内容，但只选择rating_info中的COUNT。

what I have try:

SELECT (SELECT *
AS posts
FROM   posts
WHERE  posts.id= 'bac')       AS posts,
(SELECT Count(rating_info.post_id) AS count2
FROM   rating_info
WHERE  rating_info.post_id = 'bac') AS hits 

错误结果:#1241 -操作数应该包含1列

Answer 1

这个错误是由于试图将许多列显示为帖子。

应该使用join:

SELECT *, Count(rating_info.post_id) AS count2 FROM posts
INNER JOIN rating_info ON rating_info.post_id = posts.id
WHERE posts.id = 'bac';

这将显示两个表中的所有列+计数，您可以将*切换为特定列。