Question

在SQL中，如何查询结果中某条记录的序列

现在又数据库中数据如下
id name count ...
4 A 18 ...
5 B 19 ...
6 A 19 ...
7 A 20 ...

现在我要得到 name = A count = 19 在此时的序列 ‘2’
id name count ...
1 A 18 ...
2 A 19 ...
3 A 20 ...

请问SQL怎么写啊？！大侠指教一下

Answer 1

use Tempdb
go
--> -->

declare @T table([id] int,[name] nvarchar(1),[count] int)
Insert @T
select 4,N'A',18 union all
select 5,N'B',19 union all
select 6,N'A',19 union all
select 7,N'A',20
--SQL2000
Select
[ID]=(select count(distinct [count]) from @T where [id]<=t.[id]),
[name],[count]
from @T t
where not exists(select 1 from @t where [count]=t.[count] and [id]>t.[id])

--SQL2005
select
ID=row_number()over(order by ID),
[name],[count]
from @T t
where not exists(select 1 from @t where [count]=t.[count] and [id]>t.[id])

(4 个资料列受到影响)
ID name count
----------- ---- -----------
1 A 18
2 A 19
3 A 20

(3 个资料列受到影响)

ID name count
-------------------- ---- -----------
1 A 18
2 A 19
3 A 20

(3 个资料列受到影响)

Answer 2

select id from table where name = 'A' and count = 19

Answer 3

declare @t table(id int identity(1,1),
name char(1),
count int)
insert into @t(name,count)
select name,count
from 表
order by count
select id from @t where name = 'A' and count = '19'