ROW_NUMBER 计数返回无效结果

debugcn 发表于 Dev

我玩

我对 SQL Server 很陌生，并试图提高自己。我准备了一个声明来获取被取消的客户（customerCancel 为真）

通常，当我计算取消的客户总数时，总数为 1050。

我想要做的是显示前100个用户，但是当我运行下面的查询时，我只得到38个，当我手动增加RowNumber时，结果增加但与实际结果不一样。我将使用此查询进行分页。

我的查询：

SELECT
   COUNT(*) OVER() TotalRowCount,
   ID, customerNo, customerName, customerSurname, customerTitle, customerUnitList, customerTotalList 
FROM
   (SELECT
        ROW_NUMBER() OVER(ORDER BY m.ID) RowNumber, 
        COUNT(*) OVER() TotalRowCount,
        m.ID, m.customerNo, m.customerName, m.customerSurname, m.customerTitle,
        (SELECT COUNT(f.ID) 
         FROM Invoices f 
         WHERE f.Paid = 0 
           AND f.custumerCancel = 0 
           AND f.customerID = m.ID) AS customerUnitList,
        COALESCE((SELECT SUM(f.Total) AS InvoiceNo
                  FROM Invoices f 
                  WHERE f.Paid = 0 
                    AND f.custumerCancel = 0 
                    AND f.customerID = m.ID), 0) AS customerTotalList 
    FROM
        Customers m) flist 
WHERE
   customerTotalList > 0 
   AND RowNumber between 1 AND 100

我尝试了几种方法来修复它，但没有运气。

安森·阿里卡特

试试这个查询

SELECT * FROM   (SELECT Count(*) OVER()   TotalRowCount, 
               Row_number() 
                 OVER(ORDER BY id) RowNumber, 
               id, 
               customerno, 
               customername, 
               customersurname, 
               customertitle, 
               customerunitlist, 
               customertotallist 
        FROM   (SELECT m.id, 
                       m.customerno, 
                       m.customername, 
                       m.customersurname, 
                       m.customertitle, 
                       (SELECT Count(f.id) 
                        FROM   invoices f 
                        WHERE  f.paid = 0 
                               AND f.custumercancel = 0 
                               AND f.customerid = m.id)            AS 
                       customerUnitList, 
                       Isnull((SELECT Sum(f.total) AS InvoiceNo 
                               FROM   invoices f 
                               WHERE  f.paid = 0 
                                      AND f.custumercancel = 0 
                                      AND f.customerid = m.id), 0) AS 
                       customerTotalList 
                FROM   customers m) flist 
        WHERE  customertotallist > 0) x 
WHERE  rownumber BETWEEN 1 AND 100

只有在应用所有自定义过滤器之后，您才应该应用行号过滤器进行分页。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。