使用COUNT SQL Server筛选表

Question

我是SQL的新手。我已经写了下面的代码，但却被COUNT函数卡住了。我只想在服务表上显示具有2个或多个ServiceID的ClientID。我最初尝试在Service表的联接中进行嵌套选择，但收到了错误消息。现在有了下面的代码，我得到了一个错误

消息164，级别15，状态1，第13行
每个GROUP BY表达式必须至少包含一个不是外部引用的列。

我正在努力实现以下目标。谢谢！

服务Id12313314325426>td style="text-align:right；">4274284

Answer 1

您可以按如下方式使用窗口函数count：

Select * from
(Select ...
...
Count([ServiceID]) over (partition by referral.clientid) as cnt
From ...
...
) t where cnt > 2;

请注意，order by应该是查询中的最后一个子句。相应地使用它。

根据评论中的请求，将代码添加到原始查询中，如下所示：

select * from
(select Referral.ClientID, 
Client.FirstName, 
Client.LastName, 
P.ProviderName as School,
E.ProgramID,
LI.ListLabel as Reason,
Race.ListLabel as "Race/Ethnicity",
Gender.ListLabel as "Sex/Gender",
[ServiceId],
Count([ServiceID]) over (partition by referral.clientid) as cnt -- this -- added open parenthesis before partition
FROM ProviderReferral Referral
JOIN Provider P on ReferFromProviderID=P.EntityID
JOIN ProviderReferralExt on Referral.ProviderReferralID=ProviderReferralExt.ProviderReferralID
INNER JOIN MultiSelectValue MSV on MSV.ContextID = Referral.ProviderReferralID AND 
MSV.ContextTypeID=87 AND MSV.ListID=1000001179
INNER JOIN Client on Referral.ClientID=Client.EntityID
INNER JOIN EnrollmentMember on client.EntityID=EnrollmentMember.ClientID
INNER JOIN Enrollment E on EnrollmentMember.EnrollmentID=E.EnrollmentID and E.X_CMNonCM=1
INNER JOIN ListItem LI on LI.ListValue = MSV.ListValue and LI.ListID = 1000001179
INNER JOIN ListItem Race on Race.ListValue=client.Race and Race.ListID=1000000068
INNER JOIN ListItem Gender on Gender.ListValue=Client.Gender and Gender.ListID=1
INNER JOIN Service on E.EnrollmentID=Service.EnrollmentID -- the supports table
JOIN Organization O on o.EntityID =p.OrganizationID
Where P.OrganizationID=33847
and E.ProgramID=1325 
and referral.DeletedDate = '9999-12-31' and o.DeletedDate='9999-12-31' 
and enrollmentmember.DeletedDate='9999-12-31') t
where cnt > 2 -- this
order by ClientID, FirstName