Question

我利用这一询问选择所有条款:

SELECT articles.*,categories.category_name,users.username,tags.tag 
FROM articles 
LEFT JOIN `categories` ON articles.category_id = categories.category_id 
LEFT JOIN `users` ON articles.author_id = users.user_id 
LEFT JOIN `tags` ON articles.article_id = tags.article_id 
ORDER BY articles.date_added DESC

我有另一个表格<代码>comments,我想计算一下有多少评论,在该表中该文章的背面=文章的背面。我与坦桑尼亚联合国合作组织一道进行了尝试,但结果只有一次。我怎么能用一个问题来做到这一点?

Answer 1

You can use a subquery in the SELECT clause:

SELECT articles.*,categories.category_name,users.username,tags.tag, (SELECT count(*) FROM comments c WHERE c.article_id = articles.article_id) as comments_count

Answer 2

如Arnaud576875所述,你可以使用一个子序列提取摘要数据。

我从阁下那里听到的两件事情实际上不是问题的一部分,但仍然值得指出。

you can use a table alias to shorten your SQL and make it more readable.

因此,

SELECT articles.*,categories.category_name,users.username,tags.tag 
FROM articles 
LEFT JOIN `categories` ON articles.category_id = categories.category_id 
LEFT JOIN `users` ON articles.author_id = users.user_id 
LEFT JOIN `tags` ON articles.article_id = tags.article_id 
ORDER BY articles.date_added DESC

页: 1

SELECT a.*, c.category_name, u.username, t.tag 
FROM articles a
LEFT JOIN `categories` c ON a.category_id = c.category_id 
LEFT JOIN `users` u ON a.author_id = u.user_id 
LEFT JOIN `tags` t ON a.article_id = t.article_id 
ORDER BY a.date_added DESC

I would drop SELECT * and select only the fields that you actually are going to use. This also helps with readability of your code.

友情链接