SQL每一行选择TOP 10

Question

我在选择下面代码每行的顶部时遇到了一些问题:

这将基于LineNoID，即行。

SELECT TOP 10
        DTM.[LineNoID]          as [Line ID],
        DTM.[Fault Code]        AS [DTM Fault Code],
        DTFC.[Fault Code]       AS [DTFC Fault Code], 
        COUNT(DTM.[Fault Code]) AS [Fault Code Occurences], 
        DTFC.[Fault Name],
        SUM(DTM.[Minutes Down]) AS [Total Minutes Down]
FROM
        DTMain          AS DTM,
        DTFaultCodes    AS DTFC,
        DTLine          AS DTL
WHERE   
        DTM.[Fault Code] = DTFC.[Fault Code]
AND     
        DTM.LineNoID = DTL.[LineNoID]
AND
        DTFC.[Category] = 'E'
GROUP BY 
            DTM.[LineNoID],
            DTM.[Fault Code],
            DTFC.[Fault Code],
            DTFC.[Fault Name]           
ORDER BY [Total Minutes Down] DESC

我已经看了其他几个帖子，正在努力将它们"转换"成我的代码。任何帮助将不胜感激!

谢谢,大卫。

---

更新查询:

SELECT
        [Line ID],
        [DTM Fault Code],
        [DTFC Fault Code],
        [Fault Code Occurences],
        [Total Minutes Down]
FROM
  (
SELECT
        ROW_NUMBER() OVER (PARTITION BY DTM.[LineNoID] ORDER BY SUM([Minutes Down] AS [Total Minutes Down]) DESC) [Line ID],
        DTM.[LineNoID]          AS [Line ID],
        DTM.[Fault Code]        AS [DTM Fault Code],
        DTFC.[Fault Code]       AS [DTFC Fault Code], 
        COUNT(DTM.[Fault Code]) AS [Fault Code Occurences], 
        DTFC.[Fault Name]
FROM
        DTMain          AS DTM,
        DTFaultCodes    AS DTFC,
        DTLine          AS DTL
WHERE
        DTM.[Fault Code] = DTFC.[Fault Code]
AND
        DTM.LineNoID = DTL.[LineNoID]
AND
        DTFC.[Category] = 'E'
GROUP BY
            DTM.[LineNoID],
            DTM.[Fault Code],
            DTFC.[Fault Code],
            DTFC.[Fault Name]
  ) s
WHERE [Total Minutes Down] <= 10
ORDER BY [Total Minutes Down] DESC

运行上述命令时显示"'SUM'不是可识别的内置函数名"。

为知识的缺乏道歉!

Answer 1

下面是一个基于我自己的数据库(SQL 2012)的日志表的例子:

SELECT 
    IdentifyingValue,
    GroupingValue,
    RowNum,
    SummedValue
FROM 
  (
    SELECT  
        TrgTabName AS IdentifyingValue, 
        CAST(JobDate AS DATE) GroupingValue, 
        ROW_NUMBER() OVER (PARTITION BY TrgTabName ORDER BY SUM(TrgTabNewRows) DESC) RowNum,
        SUM(TrgTabNewRows) SummedValue
    FROM DWA_JobTableCount
    GROUP BY TrgTabName, CAST(JobDate AS DATE)
  ) s
WHERE RowNum <= 3

您可以使用您的基本SELECT语句来替换示例s子查询，并修改ROW_NUMBER()函数以使用您的LineNoID作为PARTITION BY, [Total Minutes Down]作为ORDER BY DESC。然后修改外部查询以检索所需的列，并修改外部WHERE子句以获得所需的RowNum计数。

==================================================================================

编辑:

您在合并代码方面做了很好的尝试，但是有几个错误。其中之一是我的:我没有注意到您在原始查询中使用SUM()函数，我告诉您在ROW_NUMBER()函数中使用该字段。在像ROW_NUMBER()这样的窗口函数中使用任何类型的聚合都是不允许的，因此必须进行三层查询，而不是我向您展示的两层查询:最低层是原始的(包括SUM)，中间层将ROW_NUMBER()和排名添加到输出中，外部添加基于ROW_NUMBER()的过滤器。

这里还有其他几个问题:您在位置不正确的SUM()函数中包含别名，这可能是您收到的特定错误的原因。您也有两个列称为Line ID，并且您从未生成一个单独的列来给出Total Minutes Down的总和，即使您试图将其包含在SELECT和WHERE子句中。最后，您的过滤器实际上仍然没有指向ROW_NUMBER()函数产生的列，这将对您造成伤害。

步骤1:将分组和聚合放在嵌套查询中，将SUM添加为单独的列。当我在这里的时候，我还为您写了一些JOIN表达式的更新语法:继续使用这种格式，因为逗号- join不再被推荐(正如marc_s在评论中指出的那样)。

步骤2:添加ROW_NUMBER()函数生成一个新列。它使用列(Line ID和Total Minutes Down)生成一个名为RowNum的新列。

步骤3:检索所需字段并筛选"top"10 (RowNum字段有助于识别)。

看一下下面的代码，让我知道它是否适合你。我不能访问你的基表，所以有一个危险，我可能已经错误地命名了一个字段或引用了错误的别名-这是由你来审查我的代码样本，以确保它适合你的数据库。我相信这将产生您正在寻找的功能。

SELECT -- returns the data you wanted originally
    [Line ID],
    [DTM Fault Code],
    [DTFC Fault Code],
    [Fault Code Occurrences],
    [Fault Name],
    [Total Minutes Down]
FROM
  (
    SELECT -- adds the row number 
        [Line ID],
        [DTM Fault Code],
        [DTFC Fault Code],
        [Fault Code Occurrences],
        [Fault Name],
        [Total Minutes Down],
        ROW_NUMBER() OVER (PARTITION BY [Line ID] ORDER BY [Total Minutes Down] DESC) RowNum -- numbers, in descending order, the rows for each Line ID
    FROM
      (
        SELECT -- retrieves the base (aggregated) data used by the query
            DTM.[LineNoID]          as [Line ID],
            DTM.[Fault Code]        AS [DTM Fault Code],
            DTFC.[Fault Code]       AS [DTFC Fault Code], 
            COUNT(DTM.[Fault Code]) AS [Fault Code Occurrences], 
            DTFC.[Fault Name],
            SUM(DTM.[Minutes Down]) AS [Total Minutes Down] -- you still need this column to use in your output and RowNum
        FROM
            DTMain DTM 
             INNER JOIN -- note the new syntax
            DTFaultCodes DTFC ON
                DTM.[Fault Code] = DTFC.[Fault Code] -- this specifies what the relationship between the two tables is: please confirm this is correct.
             INNER JOIN 
            DTLine DTL ON 
                DTM.LineNoID = DTL.LineNoID
        WHERE DTFC.Category = 'E' -- this doesn't really need to be in the JOIN condition: because you are using INNER JOIN, it does the same thing in the WHERE clause.
        GROUP BY 
            DTM.[LineNoID],
            DTM.[Fault Code],
            DTFC.[Fault Code],
            DTFC.[Fault Name]           
      ) s1 -- the subquery at the first (lowest) level: think of it as a table with the data returned by the SELECT statement
  ) s2 -- the subquery at the second (middle) level
WHERE RowNum <= 10 -- the filter that controls the 'TOP n'