SQL 过程中确切的嵌套排名数据的确切数量

Question

我有一个这样的过程：

SELECT Name, Date, Par3, Date_rank
FROM
   (SELECT Name, ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Par3 ASC) 
   AS Date_rank
   FROM Table
   WHERE (..conditions..)) ranked
WHERE Par2_rank <= 3
ORDER BY Par2 ASC, Par3 ASC

如果行数小于或等于 3，结果是：

Name,    Date,     Par3,    Date_rank
AB   2010-01-01    1.5          1
CD   2010-02-16    0.9          1
EF   2010-02-16    1.1          2
GH   2010-02-16    1.7          3

但我只需要结果，其中我们有确切的日期行数，等于 3。所以结果应该是：

Name,    Date,     Par3,    Date_rank
CD   2010-02-16    0.9          1
EF   2010-02-16    1.1          2
GH   2010-02-16    1.7          3

我试图把WHERE Par2_rank = 3.

然后我只得到一行：

Name,    Date,     Par3,    Date_rank
GH   2010-02-16    1.7          3

我也尝试使用HAVING COUNT，但它没有给我行。

提前谢谢。

更新：感谢大家的快速回答。计数 (*( 在分区上做了这个伎俩。就是这么简单..我希望，将来多亏了你，我会更好地用SQL编码。

Answer 1

如果我

正确理解您的问题，请尝试以下方法，使用带有OVER子句的SUM()，但没有ORDER BY：

输入：

CREATE TABLE #Table (
    Name varchar(2),
    [Date] date,
    Par3 numeric(5, 1)
)
INSERT INTO #Table
    (Name, [Date], Par3)
VALUES
    ('AB', '20100101', 1.5),
    ('AB', '20100101', 1.6),
    ('AB', '20100102', 1.5),
    ('AC', '20100102', 1.6),
    ('AD', '20100102', 1.7),
    ('AE', '20100102', 1.8),
    ('CD', '20100216', 0.9),
    ('EF', '20100216', 1.1),
    ('GH', '20100216', 1.7)

T-SQL：

SELECT Name, [Date], Par3, Rn
FROM (
    SELECT 
        Name, 
        [Date], 
        Par3, 
        ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY Par3 ASC) AS Rn,
        COUNT(*) OVER (PARTITION BY [Date]) As Cnt
    FROM #Table
    ) ranked
WHERE Cnt = 3

输出：

------------------------------
Name    Date        Par3    Rn
------------------------------
CD      2010-02-16  0.9     1
EF      2010-02-16  1.1     2
GH      2010-02-16  1.7     3

Answer 2

您可以使用 EXISTS：

WITH ranked AS
(
    SELECT Name, date, ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Par3 ASC) 
       AS Date_rank
       FROM Table
       WHERE (..conditions..)
) 
SELECT 
    *
FROM ranked AS R
    WHERE EXISTS (SELECT 1
                    FROM ranked
                    where r.Date = Date
                    and Date_rank = 3
                    and Date_rank <> 4
                  )

Answer 3

WITH cte_ranked
AS
(
 SELECT Name, [Date], Par3
       ,COUNT(*) OVER (PARTITION BY [Date]) As DateCount
 FROM Table
 )
SELECT Name, [Date], Par3
FROM cte_ranked
WHERE DateCount = 3
ORDER BY [Date], Par3