問題描述

我有下表按 Id、Year DESC 排序

我想要的是一個額外的排名字段，例如:

對于有效字段中的每個更改，基本上每個 Id 交替排名.通過這種方式，我可以在 rank=1 字段上查詢，以獲得每個 Id 的所有 Valid=1 記錄，直到第一個 Valid=0.或者是否有一種更簡單的方法來選擇匹配某個條件的第一條記錄(對于 Id=1 僅前兩條記錄).我已經(jīng)玩過 ROW_NUMBER()、RANK() 和 PARTITION BY，但我似乎無法讓它工作.必須避免嵌套查詢，因為實際查詢是針對大型數(shù)據(jù)庫運行的.

有人有什么想法嗎?

謝謝和歡呼，奈奎斯特

是的，使用 Left JOIN 我們可以做到這一點.請參閱下面的代碼和結(jié)果.

第一張圖片是實際插入的數(shù)據(jù)，第二張圖片是預(yù)期的結(jié)果.

聲明@t 表(身份證號碼,_YEAR INT,有效的 TINYINT)插入 @t( id, [_YEAR], 有效 )選擇 1,2011,1UNION ALL SELECT 1,2010,1UNION ALL SELECT 1,2009,0UNION ALL SELECT 1,2002,1UNION ALL SELECT 4,2013,1UNION ALL SELECT 4,2012,1UNION ALL SELECT 4,2011,1UNION ALL SELECT 5,2013,0UNION ALL SELECT 5,2011,1UNION ALL SELECT 5,2010,1UNION ALL SELECT 6,2010,1UNION ALL SELECT 6,2011,0UNION ALL SELECT 6,2014,1選擇 q1.*來自@t q1左加入(SELECT id,MAX(_YEAR) 零年從TWHERE 有效 = 0按 ID 分組)q2ON q1.id=q2.id在哪里(q2.ID 為空)或者(q2.id 不為空且 q1.id 不為空 AND q1.id=q2.id AND q1.[_YEAR] > q2.ZeroYear)

編輯-1:在上面對列 ZeroYear 的查詢中，之前我做了 MIN(_YEAR) 但正如你在Andriy M"的評論中看到的，而不是 MIN 正確的函數(shù)是 MAX.

I have the following table ordered by Id, Year DESC

Id   Year   Valid
1    2011   1
1    2010   1
1    2009   0
1    2002   1
4    2013   1
4    2012   1
4    2011   1
etc.

What I would like to have is an extra rank field like:

Id   Year   Valid   Rank
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
etc.

Basically per Id alternating ranks for every change in the Valid field. In such a way I can query on the rank=1 field to have all Valid=1 records for each and every Id up to the first Valid=0. Or is there an easier way to select the first top number of records matching a certain condition (For Id=1 only the first two records). I already played with ROW_NUMBER(), RANK() and PARTITION BY but I can't seem to get it to work. Must avoid nesting of queries since the actual query is run against a large database.

Anyone any ideas?

Thanks and cheers, Nyquist

yes, using Left JOIN we can do that. see the below code and result.

first image is actual data inserted and second image is expected result.

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)

Edit-1: In above query for the column ZeroYear, previously i did MIN(_YEAR) but as you can see in the comment from "Andriy M" instead of MIN right function is MAX.

這篇關(guān)于如何根據(jù)列的變化值對記錄進行分組/排名?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！