将 TOP 5 与 COUNT 和 LEFT JOIN 一起使用

Question

我一直在使用 HansUp 给我的以下代码（干杯！），它运行得很好：

SELECT g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC; 

我现在正在寻找返回的前 5 个结果。我认为这很简单：

SELECT **TOP 5** g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC; 

不幸的是，这不起作用。

有没有人有什么想法。

谢谢

Answer 1

TOP 子句将根据您的第一个排序字段为您提供顶部。由于您的第一个排序字段对于所有记录都是常量 (2)，因此您将获得所有记录。将 ID 字段添加到 ORDER BY 子句中，您将只获得 5 条记录。

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#)  AS t ON g.ID = t.Grade
GROUP BY g.ID
ORDER BY g.ID, 2 DESC;

如果您实际上按 Total 降序排列在前 5 个记录之后，请将 SQL 更改为以下内容：

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#)  AS t ON g.ID = t.Grade
GROUP BY g.ID
ORDER BY Count(t.Grade) DESC , 2 DESC;

This is top by value, so if multiple records have a Total that is the same and it恰好位于前 5 个值中总而言之，你会把它们全部拿回来。如果您确实只想要返回 5 条记录，则必须对唯一的字段进行排序。

Answer 2

这应该有效。

SELECT TOP 5 g.ID, Count(t.Grade) AS Total
FROM grade AS g 
LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
GROUP BY g.ID 
ORDER BY 2 DESC

Answer 3

据我所知，这应该可行：

  SELECT TOP 5 g.ID, Count(t.Grade) AS Total
  FROM grade AS g 
  LEFT JOIN (SELECT Grade FROM telephony WHERE [Date] BETWEEN #08/16/2010# AND #08/20/2010#) AS t ON g.ID=t.Grade 
  GROUP BY g.ID 
  ORDER BY Count(t.Grade) DESC;

这里的关键点是，当您想在 WHERE 或 ORDER BY 子句中使用 SELECT 语句中的完整表达式时，请使用它。

如果您只是使用 Access 查询网格来编写 SQL，您将立即获得正确的结果（尽管您必须深入 SQL 视图来编写子查询）。