select IDENTITY(1,1) as id,name_1,name_2 from table1 where name_1='zhang'
select IDENTITY(1,1) as id,name_1,name_2 from table1 where name_1='zhang' 这条语句好像不对吧?
select (select count(*) from 表 a where a.name_1=b.name_1 and a.no<=b.no) as xu ,b.name_1,b.name_2 from 表 b where b.name_1='zhang'
create table #tb (_no int,name_1 varchar(10),name_2 varchar(10)) insert into #tb select 1000,'zhang',' yi' insert into #tb select 1001,'wang','yi' insert into #tb select 1002,'zhang','si' insert into #tb select 1003,'zhang','wu' insert into #tb select 1004,'wang','er' insert into #tb select 1005,'zhang','liu' select (select count(1) from #tb t1 where t1._no<=t2._no and t1.name_1='zhang'),name_1,name_2 from #tb t2 where name_1='zhang' drop table #tb name_1 name_2 ----------- ---------- ---------- 1 zhang yi 2 zhang si 3 zhang wu 4 zhang liu(4 row(s) affected)
仅当 SELECT 语句中有 INTO 子句时,才能使用 IDENTITY 函数。
仅当 SELECT 语句中有 INTO 子句时,才能使用 IDENTITY 函数。select IDENTITY(bigint, 1, 1) as id,name_1,name_2 into ##temptable from table1 where name_1='zhang'--向临时表里填数据 select * from ##temptable--读取带id的数据 drop table ##temptable--删除临时表
这条语句好像不对吧?
insert into #tb select 1000,'zhang',' yi'
insert into #tb select 1001,'wang','yi'
insert into #tb select 1002,'zhang','si'
insert into #tb select 1003,'zhang','wu'
insert into #tb select 1004,'wang','er'
insert into #tb select 1005,'zhang','liu'
select (select count(1) from #tb t1 where t1._no<=t2._no and t1.name_1='zhang'),name_1,name_2
from #tb t2
where name_1='zhang'
drop table #tb
name_1 name_2
----------- ---------- ----------
1 zhang yi
2 zhang si
3 zhang wu
4 zhang liu(4 row(s) affected)
select * from ##temptable--读取带id的数据
drop table ##temptable--删除临时表