按组访问TopN查询

Question

我已经审查了相当多的网站(例如Allen Brown)，用于创建按组生成前5(或N)值的查询。我想我在创建子查询时被挂起了，因为我引用的是以前的查询，而不是表。

我启动了一个按月计算PI数量(qryPICountbyMonth)的查询。目前，下面给出了一个数据不匹配表达式错误：

SELECT qryPI.EventMonth, qryPI.PI_Issue, Count(qryPI.PI_Issue) AS
CountOfPI_Issue
FROM qryPI
GROUP BY qryPI.EventMonth, qryPI.PI_Issue
HAVING (((Count(qryPI.PI_Issue)) In (Select Top 5 [PI_Issue] From [qryPI]
Where [EventMonth]=[qryPI].[EventMonth] Order By [PI_Issue] Desc)))
ORDER BY qryPI.EventMonth DESC , Count(qryPI.PI_Issue) DESC;

它是在一个单独的查询qryPI的基础上构建的

SELECT tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1) AS EventMonth, tblPI.PI_Issue
FROM tblPI
WHERE (((tblPI.EventDate) >= #4/1/2016# And (tblPI.EventDate) <= #5/31/2016#))
GROUP BY tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1), tblPI.PI_Issue;

我希望它能生成EventMonth排名前5位的PI_Issue。如果我没有提供足够的信息，请让我知道。

Answer 1

问题(或者至少是一个问题)与[EventMonth]=[qryPI].[EventMonth]有关。您的主源和查找都称为qryPI。您必须至少为其中一个设置别名。

Answer 2

你不能这样做：

HAVING (((Count(qryPI.PI_Issue)) In (Select Top 5 [PI_Issue] From [qryPI]

count(field)将返回一个整数，而不是您正在计算的一组值

我认为您可以在Access查询中指定TopN (它在属性中)，但是您必须指定一个order by子句，以便它知道如何确定TOP。您是否尝试过：

SELECT top 5 
tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1) AS EventMonth, tblPI.PI_Issue
    FROM tblPI
    WHERE (((tblPI.EventDate) >= #4/1/2016# And (tblPI.EventDate) <= #5/31/2016#))
    GROUP BY tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1), tblPI.PI_Issue
    order by PI_Issue

我也不确定为什么要在内部查询中使用GROUP BY，因为您没有返回任何聚合函数。你只是需要DISTINCT来代替吗？

尝试：

SELECT distinct top 5 
    tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1) AS EventMonth, tblPI.PI_Issue
        FROM tblPI
        WHERE (((tblPI.EventDate) >= #4/1/2016# And (tblPI.EventDate) <= #5/31/2016#))
        order by PI_Issue

实际上，如果我理解您想要的是什么，您需要的是GROUP BY而不是DISTINCT，但是您还需要返回COUNT(*)

SELECT 
  Year([eventDate]) AS yr, 
  Month([eventDate]) AS mo, 
  tblPI.PI_issue, 
  Min(tblPI.eventDate) AS MinOfeventDate, 
  Max(tblPI.eventDate) AS MaxOfeventDate, 
  Count(tblPI.PI_issue) AS CountOfPI_issue
FROM tblPI
WHERE 
  (((tblPI.EventDate)>=#4/1/2016# And 
  (tblPI.EventDate)<#6/1/2016#))
GROUP BY 
  Year([eventDate]), 
  Month([eventDate]), 
  tblPI.PI_issue;

然后，您希望在外部查询中对cnt_issue应用TOPN函数：

SELECT TOP 5 from qryInner 
order by cnt_issue desc

除了TOP5应用于所有查询结果，而不是按yy/mm分组的结果，这是我假设您想要的结果，所以尝试如下所示：

SELECT TOP 5 
qry_inner.yr, 
qry_inner.mo, 
qry_inner.CountOfPI_issue, 
qry_inner.PI_issue, 
qry_inner.MinOfeventDate, 
qry_inner.MaxOfeventDate
    FROM qry_inner
    ORDER BY qry_inner.CountOfPI_issue DESC;

据我所知，Access不允许您选择组中最多的行数，因此您需要将外部查询结果限制为一个月，然后应用TOP函数。