sql - 如何根据列的变化值对记录进行分组/排名？

Question

我有下表按 Id、Year DESC 排序

ID 年份有效
1 2011 1
1 2010 1
1 2009 0
1 2002 1
4 2013 1
4 2012 1
4 2011 1
等等。

我想要的是一个额外的排名字段，如:

Id 年份有效等级
1 2011 1 1
1 2010 1 1
1 2009 0 2
1 2002 1 3
4 2013 1 1
4 2012 1 1
4 2011 1 1
等等。

基本上每个 Id 交替排列有效字段中的每个更改。通过这种方式，我可以在 rank=1 字段上查询，以获得每个 Id 的所有 Valid=1 记录，直到第一个 Valid=0。或者是否有一种更简单的方法来选择匹配某个条件的第一条记录(对于 Id=1 仅前两条记录)。我已经玩过 ROW_NUMBER()、RANK() 和 PARTITION BY，但我似乎无法让它工作。必须避免嵌套查询，因为实际查询是针对大型数据库运行的。

有人有什么想法吗？

谢谢和欢呼，
奈奎斯特

Answer 1

是的，使用 Left JOIN 我们可以做到这一点。
请参阅下面的代码和结果。

第一张图片是插入的实际数据，第二张图片是预期的结果。

DECLARE @t TABLE
(
    id      INT
    ,_YEAR  INT
    ,valid  TINYINT
)
INSERT INTO @t( id, [_YEAR], valid )
            SELECT 1,2011,1
UNION ALL   SELECT 1,2010,1
UNION ALL   SELECT 1,2009,0
UNION ALL   SELECT 1,2002,1
UNION ALL   SELECT 4,2013,1
UNION ALL   SELECT 4,2012,1
UNION ALL   SELECT 4,2011,1
UNION ALL   SELECT 5,2013,0
UNION ALL   SELECT 5,2011,1
UNION ALL   SELECT 5,2010,1
UNION ALL   SELECT 6,2010,1
UNION ALL   SELECT 6,2011,0
UNION ALL   SELECT 6,2014,1


SELECT  q1.*
FROM @t q1
LEFT JOIN 
(
    SELECT id,MAX(_YEAR) ZeroYear
    FROM @t
    WHERE valid = 0
    GROUP BY id
)q2
    ON q1.id=q2.id
WHERE 
(q2.ID IS NULL)
OR
(q2.id IS NOT NULL AND q1.id IS NOT NULL AND q1.id=q2.id AND q1.[_YEAR] > q2.ZeroYear)

编辑 1:
在上面对列 ZeroYear 的查询中，以前我做了 MIN(_YEAR) 但正如你在“Andriy M”的评论中看到的，而不是 MIN 正确的函数是 MAX。