sql文

[niuniu777]

字段和数据
code1 code2 name
0001 101 li1
0001 552 li2
0001 556 li3
0002 000 li4
0002 666 li5
0003 000 li6

我想得到No的数据：
code1 code2 name No
0001 101 li1 1
0001 552 li2 2
0001 556 li3 3
0002 000 li4 1
0002 666 li5 2
0003 000 li6 1

一条SQL 文实现，不用临时表

[liuxuan123]

declare @tb table (code1 varchar(10),code2 varchar(10),name varchar(10))
insert into @tb select '0001','101','li1'
insert into @tb select '0001','552','li2'
insert into @tb select '0001','556','li3'
insert into @tb select '0002','000','li4'
insert into @tb select '0002','666','li5'
insert into @tb select '0003','000','li6'

select *,[no]=(select count(1) from @tb where code1=t.code1 and name<=t.name) from @tb t

[pt1314917]

select *,no=(select count(1) from tb where code1=a.code1 and code2 <=a.code2) from tb a

[kk19840210]

declare @tb table (code1 varchar(10),code2 varchar(10),name varchar(10))
insert into @tb select '0001','101','li1'
insert into @tb select '0001','552','li2'
insert into @tb select '0001','556','li3'
insert into @tb select '0002','000','li4'
insert into @tb select '0002','666','li5'
insert into @tb select '0003','000','li6'


select *,[No]=(select count(1) from @tb where code1=a.code1 and [name]<=a.name) from @tb a

code1      code2      name       No
---------- ---------- ---------- -----------
0001       101        li1        1
0001       552        li2        2
0001       556        li3        3
0002       000        li4        1
0002       666        li5        2
0003       000        li6        1

(6 行受影响)

[wzy_love_sly]

select *,[no]=(select count(1) from tb where code1=t.code1 and code2<=t.code2) from tb t

[xiaomeixiang]

select *,no=(select count(*) from tb1 where code1=a.code1 and code2<=a.code2)
from tb1 a

[wzy_love_sly]

declare @tb table (code1 varchar(10),code2 varchar(10),name varchar(10))
insert into @tb select '0001','101','li1'
insert into @tb select '0001','552','li2'
insert into @tb select '0001','556','li3'
insert into @tb select '0002','000','li4'
insert into @tb select '0002','666','li5'
insert into @tb select '0003','000','li6'

select *,[no]=(select count(1) from @tb where code1=t.code1 and name<=t.name) from @tb t

code1 code2 name no
0001 101 li1 1
0001 552 li2 2
0001 556 li3 3
0002 000 li4 1
0002 666 li5 2
0003 000 li6 1

[dobear_0922]

create table tb(code1 varchar(8), code2 varchar(8), name varchar(16))
insert tb select '0001', '101', 'li1' 
union all select '0001', '552', 'li2' 
union all select '0001', '556', 'li3' 
union all select '0002', '000', 'li4' 
union all select '0002', '666', 'li5' 
union all select '0003', '000', 'li6'

select t.*, [No]=(select count(1)+1 from tb where code1=t.code1 and name<t.name) 
from tb t

/*
code1    code2    name             No
-------- -------- ---------------- -----------
0001     101      li1              1
0001     552      li2              2
0001     556      li3              3
0002     000      li4              1
0002     666      li5              2
0003     000      li6              1

(6 row(s) affected)
*/

drop table tb

[nzperfect]

declare @tb table (code1 varchar(10),code2 varchar(10),name varchar(10))
insert into @tb select '0001','101','li1'
insert into @tb select '0001','552','li2'
insert into @tb select '0001','556','li3'
insert into @tb select '0002','000','li4'
insert into @tb select '0002','666','li5'
insert into @tb select '0003','000','li6'
select *,ROW_NUMBER() OVER(ORDER BY code1,code2 asc) AS 'Row Number' from @tb