Question

I m与SQ服务器数据库合作。 Say, 我选择了一批身份识别器。我能否找到一个在那个阵列中平等的NID的顺序?

我这里指的是什么。这是我的实际选择:

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”rel=“nofollow”http://msdn.microsoft.com/en-us/library/ms186734.aspx

Answer 2

Try this:

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

Try this... Though I m not sure on your table structure

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;

友情链接