如何在使用GROUP BY和Aggregates时对Server行进行分页

Question

我正在尝试对Server行进行分页。我在网上找到了一些教程，但是我没有掌握每一个细节，这使我在使他们的例子适应现有的查询方面失败了。

教程的说明：

USE AdventureWorks2008R2
GO
SELECT
  m.RowNr,
  m.SalesOrderID,
  m.SalesOrderDetailID,
  m.OrderQty,
  m.ProductID,
  m.UnitPrice,
  m.LineTotal,
  m.rowguid,
  m.ModifiedDate
FROM
  (
    SELECT
      ROW_NUMBER() OVER(ORDER BY SalesOrderDetailID DESC) AS RowNr,
      SalesOrderID,
      SalesOrderDetailID,
      OrderQty,
      ProductID,
      UnitPrice,
      LineTotal,
      rowguid,
      ModifiedDate
    FROM Sales.SalesOrderDetail AS tbl
  )m
WHERE RowNr BETWEEN 1 AND 10;

我现有的(非分页)查询：

SELECT DISTINCT
  MAX(i.ID) AS ID,
  i.ItemLookupCode,
  MAX(CAST(i.Notes AS varchar(max))) AS Notes,
  MAX(CONVERT(varchar(30), i.Price, 1)) AS Price,
  MAX(i.PictureName) AS PictureName

FROM Item AS i

LEFT JOIN nitroasl_pamtable AS n
  ON i.ID = n.ItemID

WHERE ( i.ID LIKE '%hdmi%' OR i.ItemLookupCode LIKE '%hdmi%' OR i.Notes LIKE '%hdmi%' OR i.Description LIKE '%hdmi%' OR i.ExtendedDescription LIKE '%hdmi%' OR n.ManufacturerPartNumber LIKE '%hdmi%' OR n.PAM_Keywords LIKE '%hdmi%' )
AND ( i.WebItem = 0 AND i.Price > 0 )

GROUP BY i.ItemLookupCode

ORDER BY i.ItemLookupCode ASC;

我的尝试

/* Return Page */
SELECT
  i.RowID,
  MAX(i.ID) AS ID,
  i.ItemLookupCode,
  MAX(CAST(i.Notes AS varchar(max))) AS Notes,
  MAX(CONVERT(varchar(30), i.Price, 1)) AS Price,
  MAX(i.PictureName) AS PictureName
FROM (
  SELECT

    /* The following row is the line that is being blamed by the error */
    ROW_NUMBER() OVER(ORDER BY ItemLookupCode) AS RowID,

    MAX(ID) AS ID,
    ItemLookupCode,
    MAX(CAST(Notes AS varchar(max))) AS Notes,
    MAX(CONVERT(varchar(30), Price, 1)) AS Price,
    MAX(PictureName) AS PictureName
  FROM Item AS tbl
) AS i

WHERE RowID BETWEEN 1 AND 15;

上面的查询返回以下错误：

Msg 8120、级别16、状态1、第12行列'Item.ItemLookupCode‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP子句中。

任何熟悉Server (2008 R2)的人能帮我填补这里的空白吗？

Answer 1

您需要对分组结果应用行号。

WITH cteresults AS 
(
    SELECT DISTINCT Max(i.id) AS ID, 
        i.itemlookupcode, 
        Max(Cast(i.notes AS VARCHAR(max))) AS Notes, 
        Max(CONVERT(VARCHAR(30), i.price, 1)) AS Price, 
        Max(i.picturename) AS PictureName 
    FROM   item AS i 
        LEFT JOIN nitroasl_pamtable AS n ON i.id = n.itemid 
    WHERE  ( i.id LIKE '%hdmi%' 
            OR i.itemlookupcode LIKE '%hdmi%' 
            OR i.notes LIKE '%hdmi%' 
            OR i.description LIKE '%hdmi%' 
            OR i.extendeddescription LIKE '%hdmi%' 
            OR n.manufacturerpartnumber LIKE '%hdmi%' 
            OR n.pam_keywords LIKE '%hdmi%' ) 
        AND (i.webitem = 0 AND i.price > 0) 
    GROUP  BY i.itemlookupcode
) 
,ctepagination AS 
(
    SELECT *, Row_number() OVER(ORDER BY itemlookupcode) AS RowID 
    FROM   cteresults
) 

SELECT * 
FROM   ctepagination 
WHERE  rowid BETWEEN 1 AND 15 

在没有CTE的情况下，这应该是可行的。但是，不确定它是否解决了你在评论中提到的问题。

SELECT * FROM 
(
    SELECT *, Row_number() OVER(ORDER BY itemlookupcode) AS RowID from
    (
        SELECT DISTINCT Max(i.id) AS ID, 
            i.itemlookupcode, 
            Max(Cast(i.notes AS VARCHAR(max))) AS Notes, 
            Max(CONVERT(VARCHAR(30), i.price, 1)) AS Price, 
            Max(i.picturename) AS PictureName 
        FROM   item AS i 
            LEFT JOIN nitroasl_pamtable AS n ON i.id = n.itemid 
        WHERE  ( i.id LIKE '%hdmi%' 
                OR i.itemlookupcode LIKE '%hdmi%' 
                OR i.notes LIKE '%hdmi%' 
                OR i.description LIKE '%hdmi%' 
                OR i.extendeddescription LIKE '%hdmi%' 
                OR n.manufacturerpartnumber LIKE '%hdmi%' 
                OR n.pam_keywords LIKE '%hdmi%' ) 
            AND (i.webitem = 0 AND i.price > 0) 
        GROUP  BY i.itemlookupcode
    ) t1
) t2
WHERE  rowid BETWEEN 1 AND 15