SQL - 如何在联合查询中仅计算一个表的条目数

Question

这似乎是一个常规操作，但我似乎找不到如何做到这一点。

我有一个连接查询。

 SELECT a.nom_batim, COUNT(b.maxten) AS NumFaulty
 FROM tblTrials AS b, tblRooms AS a
 WHERE b.batiment = a.batiment
 AND b.maxten > 10
 GROUP BY a.nom_batim
 ORDER BY a.nom_batim

我希望只返回tblTrials条目的计数。然而，由于我不知道如何编写代码，它目前正在计算所有出现的b.maxten > 10和b.batiment = a.batiment的次数。我在表格中有1个实际的b.maxten > 10出现，但有231个b.batiment = a.batiment出现（这些表格设置得很糟糕，不是我的选择；对我来说，这些表格被认为是只读的），因此它返回了231个计数。

如何正确地COUNT(b.maxten)，但仍以用户友好的方式显示a.nom_batim作为batiment ID字段的表示形式？（a.nom_batim是建筑物#batiment的完整名称）

更新
到目前为止，这就是我所做的..

SELECT a.nom_batim, COUNT(b.batiment) AS NumFaulty
FROM (SELECT DISTINCT nom_batim, batiment FROM tblRooms) AS a
INNER JOIN tblTrials AS b ON a.batiment = b.batiment
WHERE b.maxten > 10
GROUP BY a.nom_batim
ORDER BY a.nom_batim

它可以工作，但当我只需要从tblRooms获取最多30个值时，似乎会占用大量资源，但必须查询所有5000多行，仅选择不同的batiment值。是否没有不使用batiment表tblBatiment: batiment，nom_batim就能完成这项任务的方法？ 我知道这是最好的方法，但我无法访问。

- StuckAtWork

Answer 1

您可以在子查询中执行计数，这样它只适用于一个表的记录：

SELECT ..
FROM (SELECT batiment, COUNT(maxten) FROM tblTrials WHERE maxten > 10) AS b
    ,tblRooms AS a
...

否则，计数将应用于最终结果中的所有记录，因为查询引擎不区分来自一个地方或另一个地方的记录。

- mellamokb

---

实际上，我在发布后就遇到了这个问题（似乎经常发生），但是我认为额外的查询可能会降低性能。然而，似乎没有其他简单的方法... - StuckAtWork

不应该太低效。所有记录将被分组并计数一次，然后为结果组合一次。如果您想确保，可以查看查询计划。 - mellamokb

结果证明，你不能这样做...它期望batiment作为聚合函数的一部分，这对用户不友好。我需要使用FROM（SELECT...）填充nom_batim，而不是在tblTrials上使用FROM（SELECT...）。不知道为什么，但它有效。 - StuckAtWork

Answer 2

回到您最初的查询，如果tblTrials表上有一个身份列，您就可以得到您想要的内容：

SELECT a.nom_batim, COUNT(distinct b.id) AS NumFaulty
FROM tblTrials b INNER JOIN tblRooms a   
     ON b.batiment = a.batiment
WHERE b.maxten > 10
GROUP BY a.nom_batim
ORDER BY a.nom_batim  

我还用正确的连接语法（使用“join”关键字）替换了您的连接语法。

- Gordon Linoff

---

我没有一个适当的索引，它是由3个字段组合而成的。有没有一种方法可以COUNT(distinct b.fieldA, b.fieldB, b.fieldC)？再次强调，数据库设计不是我的错，我只需要在其上编程。 - StuckAtWork

你可以将这些字段合并在一起，并进行计数，就像count(distinct fieldA+';'+fieldB+';'+fieldC)这样。这是SQL Server的语法，我不知道在Access中字符串如何合并。此外，您可能需要使用cast()函数将非字符数据类型转换为字符。 - Gordon Linoff

比什么更或者不如效率高？查询的效率高度依赖于SQL优化器。这个查询似乎会进行两次表扫描、一次连接、一次聚合，并且还需要额外的工作来去重。此外，连接是等值连接，可以通过建立索引来进行优化。没有理由认为这个查询的性能会比返回相同结果的其他结构差。 - Gordon Linoff

Answer 3

尝试使用：

HAVING b.maxten>10

- SoEnLion

Answer 4

试试这个：

 SELECT a.nom_batim, COUNT(b.maxten) AS NumFaulty
 FROM tblTrials AS b, tblRooms AS a
 WHERE b.batiment = a.batiment
 GROUP BY a.nom_batim
 HAVING count(b.maxten) > 10
 ORDER BY a.nom_batim

- aF.

---

我不相信这是正确的...会导致错误。由于我无法聚合出b.maxten（因为计数将产生maxten列表，总是计数为1），所以这行不通。 - StuckAtWork

抱歉，我错过了它的计数！ - aF.

现在它只返回具有超过10个maxten条目的建筑物。= P Maxten是我正在进行的简单试验条件的简单名称；它意味着数字字段“maxten”的值应小于11，而不是建筑物需要少于10个此字段的条目。对于造成的困惑，我感到抱歉。 - StuckAtWork

Answer 5

到目前为止，我能做到最好的方法是：

SELECT a.nom_batim AS Building, Count(q.batiment) AS Fixes
FROM (SELECT DISTINCT nom_batim, batiment FROM tblRooms)  AS a 
INNER JOIN tblTrials AS q 
ON a.batiment = q.batiment
WHERE q.maxten > 10
GROUP BY a.nom_batim

看起来 SELECT DISTINCT nom_batim, batiment FROM tblRooms 要慢一些，因为 tblTrials 可能包含 60k 条记录，而 tblRooms 可能包含 10k 条记录... 但是这些记录还没有输入，所以我无法真正测试它。Gordon 指出如果返回的是相同的内容，则可能速度相同。我有多字段主键，所以这也可能有所帮助（也许不像 ID 字段那样有帮助，但你还能怎么办呢）。

感谢其他回答者。

- StuckAtWork