SQL subQuery with COUNT() in WHERE/HAVING Statement

Question

请考虑以下子查询。此查询检索拥有 1 个以上"IDKleerkastPersoon"的员工的所有个人 ID。对于此查询，我在 HAVING 子句中使用了 COUNT（） 语句。此子返回该员工的所有个人 ID。

(SELECT     DISTINCT Persoon_1.Stamnr
 FROM          dbo.KleerkastPerPersoon AS KleerkastPerPersoon_1 INNER JOIN
                                               dbo.Persoon AS Persoon_1 ON KleerkastPerPersoon_1.ID_Persoon = Persoon_1.ID_Persoon
                        GROUP BY Persoon_1.Stamnr, Persoon_1.ID_Afdeling, KleerkastPerPersoon.IDKleerkastPersoon, Persoon.Naam
                        HAVING      (Persoon_1.ID_Afdeling = 2) AND (COUNT(KleerkastPerPersoon.IDKleerkastPersoon) >= 2)
                        ORDER BY Persoon_1.Stamnr DESC)

现在用户要求更多信息，然后仅要求员工的个人ID。所以我在上面写了一个查询（见下文），检索有关员工的更多全局信息。正如预期的那样，sql 服务器拒绝此方法。

SELECT     dbo.Persoon.Stamnr, dbo.Persoon.Naam, dbo.Persoon.Voornaam, dbo.Refter.RefterOmschrijving, dbo.Kleedkamer.KleedkamerOmschrijving, 
                  dbo.Kleerkast.KleerkastOmschrijving
FROM         dbo.KleerkastPerPersoon INNER JOIN
                  dbo.Persoon ON dbo.KleerkastPerPersoon.ID_Persoon = dbo.Persoon.ID_Persoon INNER JOIN
                  dbo.Kleerkast INNER JOIN
                  dbo.Kleedkamer ON dbo.Kleerkast.ID_Kleedkamer = dbo.Kleedkamer.ID_Kleedkamer INNER JOIN
                  dbo.Refter ON dbo.Kleedkamer.ID_Refter = dbo.Refter.ID_Refter ON dbo.KleerkastPerPersoon.ID_Kleerkast = dbo.Kleerkast.ID_Kleerkast
WHERE     (dbo.Persoon.Stamnr IN <<<Result of my first subquery>>>
                     )

引发的错误消息是：

An aggregate may not appear in the where clause unless it is in a subquery contained in a having clause or a select list, and the column being aggregated is an outer reference

所以我将我的子查询移到了 having 子句：

<<<Query 2>>>
HAVING      (dbo.Persoon.Stamnr IN
                      (SELECT     TOP (100) PERCENT Persoon_1.Stamnr
                        FROM          dbo.KleerkastPerPersoon AS KleerkastPerPersoon_1     INNER JOIN
                                               dbo.Persoon AS Persoon_1 ON KleerkastPerPersoon_1.ID_Persoon = Persoon_1.ID_Persoon
                        GROUP BY Persoon_1.Stamnr, Persoon_1.ID_Afdeling
                        HAVING      (Persoon_1.ID_Afdeling = 2) AND (COUNT(KleerkastPerPersoon.IDKleerkastPersoon) >= 2)
                        ORDER BY Persoon_1.Stamnr DESC))

但现在查询不返回任何结果。虽然我应该看到 98 条不同的记录。你们中是否有人有解决我的问题或解决方法的方法？例如，如何消除对子查询的需求。

Answer 1

将第一个子查询的结果存储在表变量中，然后在最终查询中使用它。此外，除非您使用 TOP，否则子查询中也不需要 Order By 子句。

Answer 2

好吧，

如果我得到你对类似的东西的看法

SELECT dbo.Persoon.Stamnr, dbo.Persoon.Naam, dbo.Persoon.Voornaam, dbo.Refter.RefterOmschrijving, dbo.Kleedkamer.KleedkamerOmschrijving, dbo.Kleerkast.KleerkastOmschrijving
FROM dbo.KleerkastPerPersoon
inner join (your original query) SomeSuitableAlias
On SomeSuitableAlias.Stamnr = dbo.KleerkastPerPersoon.Stamnr 

应该做这项工作。

Answer 3

我认为错误消息中的聚合是指第一个查询中的GROUP BY子句和COUNT(KleerkastPerPersoon.IDKleerkastPersoon)。

当您将此选择用作子查询时，您显然引用的内容不是 HAVING 子句或 SELECT 语句的结果。应在子查询的结果集中包含所需的任何列或聚合。您还应该在子查询中省略ORDER BY部分（假设您还没有这样做）。

我无法确切看到有问题的引用是什么，但我同意 mhasan 的观点，即您应该至少在最初使用一些临时表来分解您的查询，并在您让它工作时将它们全部重新组合到一个查询中。