时间戳

首页 > 解决方案 > SQL 具有分区列的子句

sql-server - SQL 具有分区列的子句

问题描述

当我使这个查询不嵌套（包括--'s）时，我得到了我正在寻找的东西。但是，我只想要行号（rn）<=10。然后当我嵌套它时，我收到一个关于我的 ORDER BY 和 HAVING 的错误。

--SELECT *
--FROM(
    SELECT InvoiceNo, DescriptionofService, SUM(Price) AS Total_Price,year(invoicedate) AS 'Year', COUNT(DescriptionofService) AS cnt
        ,ROW_NUMBER() OVER (PARTITION BY InvoiceNo ORDER BY COUNT(DescriptionofService) DESC, SUM(Price) DESC) AS rn
        FROM Billing_Lab_Invoice
            WHERE year(Invoicedate) = 2020 
            GROUP BY InvoiceNo, DescriptionofService, year(invoicedate)
            ORDER BY InvoiceNo, cnt DESC, Total_Price DESC
--          ) 
--  HAVING rn <= 10

这是我在尝试过滤行号列之前的读数

64941452    A   87.50   2020    25  1
64941452    B   62.50   2020    25  2
64941452    C   81.00   2020    18  3
64941452    D   44.00   2020    11  4
......
64941452    E   22.50   2020    3   14

编辑 这有效，但不知道为什么

SET DATEFORMAT DMY
;WITH CTE AS (  
    SELECT InvoiceNo
        , DescriptionofService
        , SUM(Price) AS Total_Price
        ,year(invoicedate) AS 'Year'
        , COUNT(DescriptionofService) AS cnt
        ,ROW_NUMBER() OVER
            (
            PARTITION BY InvoiceNo ORDER BY COUNT(DescriptionofService) DESC, SUM(Price) DESC) AS rn 
        FROM Billing_Lab_Invoice
            WHERE year(Invoicedate) = 2020 
            GROUP BY InvoiceNo, DescriptionofService, year(invoicedate)
            )
SELECT * FROM CTE WHERE [rn] <=10

64941452 A 87.50 2020 25 1 64941452 B 62.50 2020 25 2 64941452 C 81.00 2020 18 3 64941452 D 44.00 2020 11 4 64941452 D 50.00 2020 10 5 64941452 E 161.00 2020 7 6 64941452 F 22.50 2020 6 7 64941452 G 504.352020 5 8 64941452 H 40.00 2020 5 9 64941452 I 40.00 2020 5 10 64946394 A 351.00 2020 78 1 64946394 B 199.50 2020 57 2

标签： sql-servernestedsql-order-bypartitionhaving-clause

解决方案

像这样修改你的原件SELECT：

SELECT x.* FROM (
    
    SELECT TOP 100 PERCENT 
        InvoiceNo, DescriptionofService, SUM(Price) AS Total_Price,year(invoicedate) AS 'Year', COUNT(DescriptionofService) AS cnt
        ,ROW_NUMBER() OVER (PARTITION BY InvoiceNo ORDER BY COUNT(DescriptionofService) DESC, SUM(Price) DESC) AS rn
    FROM Billing_Lab_Invoice
    WHERE year(Invoicedate) = 2020 
    GROUP BY InvoiceNo, DescriptionofService, year(invoicedate)
    ORDER BY InvoiceNo, cnt DESC, Total_Price DESC

) AS x
WHERE x.rn <= 10;

从技术上讲，除非您指定TOP. 使用TOP 100 PERCENT解决这个问题，返回所有匹配的记录。

推荐阅读