34,576
社区成员
发帖
与我相关
我的任务
分享
--沒問題
if object_id('tempdb..#')is not null drop table #
go
create table #(id int, keywords varchar(20))
----- --------
insert # select 1, '123ABC65'
insert # select 2 ,'6523ABCKJDFG'
insert # select 3 ,'32432'
insert # select 4 ,'SADFSFABC'
insert # select 5, 'ABC1234'
insert # select 6, 'ASFDS'
SELECT Keywords ,(SELECT COUNT(*)+1 FROM (SELECT * FROM # WHERE Keywords like '%ABC%') A WHERE A.ID <B.ID) AS Rowno
FROM (SELECT * FROM # WHERE Keywords like '%ABC%') B
/*Keywords Rowno
-------------------- -----------
123ABC65 1
6523ABCKJDFG 2
SADFSFABC 3
ABC1234 4*/
--TRY
SELECT A,(SELECT COUNT(*)+1 FROM (SELECT * FROM TB WHERE A>2)A WHERE A.A<B.A) FROM (
SELECT * FROM TB WHERE A>2)B
if object_id('tb')is not null drop table tb
go
create table tb(a int )
insert tb select 1
insert tb select 3
insert tb select 0
insert tb select 100
select a,cnt=(select count(*)+1 from tb where a<t.a) from tb t order by cnt
/*a cnt
----------- -----------
0 1
1 2
3 3
100 4*/
select * , rowno = (select count(1) from table1 where field1 < t.field1 or (field1 = t.field1 and field1 < t.field2) ) + 1 from table1 t