选择具有特定计数的记录

Question

我想细化一个查询甚至进一步阐述结果集。 我有一张桌子Log

我每天早上运行这个脚本。

SELECT [90DayTally] = COUNT(AssociateName) 
, AssociateName 
, Supervisor 
FROM [DailyAttendance].[dbo].[Log] 
Where EventDate >= dateadd(day, datediff(day, 0, getdate()), -90) 
group by AssociateName, Supervisor 
having COUNT(associatename)>4 
Order By [90DayTally] Desc 

这给管理人员看到了谁在搞乱（LOL）所以这是查询的结果。

90DayTally AssociateName  Supervisor 
7   Carlos Silva  Karla Vivas 
6   Luz Carmen Garcia Cynthia Perez 
5   Daniel Lockington Damian Vasquez 

我也想向高层管理人员展示这些colum计数究竟是什么。 下面我显示的是什么，我想看到一个例子，我有到达致这现在

select * 
FROM [DailyAttendance].[dbo].[Log] 
Where EventDate >= dateadd(day, datediff(day, 0, getdate()), -90) 
and AssociateName in ('Carlos Silva', 'Luz Carmen Garcia', 'Daniel Lockington') 
order by AssociateName Asc 

这是工作显示这些结果的基本的方式，但我有这个每天早上做的，改变了AssociateName非常乏味。

我已经尝试了子查询，但显然incorrectly.Is有办法可以达致这无需输入where子句下每个AssociateName？

Answer 1

您可以使用您现有的查询作为子查询并替换IN子句的硬编码值。

事情是这样的，也许： -

select * 
FROM [DailyAttendance].[dbo].[Log] 
Where EventDate >= dateadd(day, datediff(day, 0, getdate()), -90) and AssociateName in (
SELECT AssociateName FROM (
SELECT [90DayTally] = COUNT(AssociateName), AssociateName, Supervisor 
FROM [DailyAttendance].[dbo].[Log] 
Where EventDate >= dateadd(day, datediff(day, 0, getdate()), -90) 
group by AssociateName, Supervisor 
having COUNT(associatename)>4 
) AS DATA 
) order by AssociateName Asc