SQL：从一个表获取所有记录，并从第二个表获取记录计数？ [英] SQL: Get all records from one table AND a count of records from a second table?

问题描述

假设有两个表格：

  messageID / Message / More .. 
 1 /这是第一条消息/ etc .. 
 2 /这是第二条消息/ etc .. 
 3 /这是第三封邮件/ Etc .. 
  

表B

  commentID / messageID /注释
 1/2 /这是对第二条消息的注释
 2/2 /这是对第二个消息的另一个注释
 3/3 /这是对第三个消息的注释
  

表之间的关系是 messageID 字段。

我想要一个查询生成这样的结果，表A中的字段以及来自表B的每个消息的注释数量，如下：

  messageID / Message / More ... / CommentCount 
 1 /这是第一个消息/ etc ... / 0 
 2 /这是第二个消息/ etc ... / 2 
 3 /这是第三个消息/ etc ... / 1 
  

我试过这样： / p>

  SELECT tableA。*，count（commentID）as commentcount 
 FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB。 messageID GROUP BY messageID 
  

但不起作用。有任何想法吗？似乎应该可以在一个查询中执行此操作。我使用MSSQL。非常感谢您的帮助。

解决方案

标量子查询将工作：

  SELECT tableA。* 
，（SELECT count（commentID）FROM tableB WHERE tableA.messageID = tableB.messageID）as commentcount 
 FROM tableA 
  

像往常一样，有很多方法来皮肤这种猫，具有不同的性能配置文件。

当使用 GROUP BY 时，输出中的所有列都需要在 GROUP BY 或在聚合函数中 - 即使消息ID中的其他列没有变化，仍然需要在 GROUP BY 中。

Say there are two tables:

TABLE A

messageID / Message                     / More..
1         / This is the first message   / Etc..
2         / This is the second message  / Etc..
3         / This is the third message   / Etc..

TABLE B

commentID / messageID / Comment 
1         / 2         / This is a comment to the second message 
2         / 2         / This is another comment to the second message 
3         / 3         / This is a comment to the third message

The tie between the tables is the messageID field.

I would like one query that generates results like this, where I pull ALL the fields out of Table A, and a count of the number of comments for each message from Table B, like so:

messageID  / Message                    / More...  / CommentCount
1          / This is the first message  / etc...   / 0
2          / This is the second message / etc...   / 2
3          / This is the third message  / etc...   / 1

I have tried something like this:

SELECT tableA.*, count(commentID) as commentcount 
FROM tableA LEFT JOIN tableB ON tableA.messageID = tableB.messageID GROUP BY messageID

but it doesn't work. Any ideas? It seems like it should be possible to do this in one query. I'm using MSSQL. Thanks for any help.

解决方案

Scalar subquery will work:

SELECT tableA.*
    ,(SELECT count(commentID) FROM tableB WHERE tableA.messageID = tableB.messageID) as commentcount 
FROM tableA

As usual, there are a lot of ways to skin this cat, with varying performance profiles.

When using a GROUP BY, all columns in the output either need to be in the GROUP BY or in aggregate functions - even though there is no variation in the other columns within a messageID, they still would need to be in the GROUP BY.

这篇关于SQL：从一个表获取所有记录，并从第二个表获取记录计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！