SQL, чтобы получить порядковый индекс в выборе для определенного идентификатора

Question

Я работаю с базой данных SQL Server. Скажем, у меня есть выборка, которая возвращает массив идентификаторов. Могу ли я узнать (с помощью SQL) порядок идентификатора равный N в этом массиве?

Вот что я имею в виду. Это мой фактический выбор:

SELECT [id] FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 
ON t1.[id]=t2.[itmid] 
ORDER BY t2.[iid] ASC;

Скажем, он вернет следующий массив идентификаторов:

13, 7, 34, 5, 2, 14

И, скажем, мне нужно знать, какой индекс ID=5 в этом массиве (ответ равен 3, если 13 - это индекс 0, 7 = индекс 1, 34 = индекс 2, 5 = становится индексом 3.)

Answer 1

Попробуйте использовать ROW_NUMBER():

SELECT ROW_NUMBER() OVER (ORDER BY t2.[iid] ASC) -1 AS RowIndex
    ,[id] 
FROM [dbo.test_db_002] t1
LEFT JOIN [dbo.test_db_003] t2 
    ON t1.[id]=t2.[itmid] 
ORDER BY t2.[iid] ASC;

Подробнее об этом здесь http://msdn.microsoft.com/en-us/library/ms186734.aspx

Answer 2

Попробуй это:

SELECT x.RowNum - 1
FROM
(
    SELECT [id], ROW_NUMBER() OVER (ORDER BY t2.[iid]) as RowNum 
    FROM   [dbo.test_db_002] t1
           LEFT JOIN [dbo.test_db_003] t2 ON t1.[id]=t2.[itmid] 
) x
WHERE x.[id] = 5

Обратите внимание, что -1 это потому что ROW_NUMBER() начинается с 1 вместо 0, и вы специально упомянули массив с нулевым индексом.

Answer 3

Попробуйте это... Хотя я не уверен в вашей структуре таблицы

Declare @Temp table
(
    id int,
    Name varchar(20)
)
Insert into @Temp 
    select 1, 'Bob'
union all
    select 2, 'Mark'
union all
    select 3, 'Shaun'
union all
    select 4, 'Ryan'
union all
    select 5, 'Steve'
union all
    select 6, 'Bryan'
union all
    select 7, 'Henry'

Declare @Temp2 table
(
    iid int,
    itmid int,
    Name varchar(20)
)
Insert into @Temp2
    select 1, 3, 'Thing'
union all
    select 2, 2, 'This'
union all
    select 3, 5, 'That'
union all
    select 4, 1, 'They'
union all
    select 5, 3, 'There'
union all
    select 6, 5, 'Though'
union all
    select 7, 6, 'Thought'

SELECT t1.[id], Row_Number() OVER (Order by t1.[id]) as RowNum
FROM @Temp t1
LEFT JOIN @Temp2 t2 ON t1.[id]=t2.[itmid] 
ORDER BY t1.[id] ASC;