select IDENTITY(int,1,1) as id,* into #t from 表 order by Nametruncate table 表insert 表 select Name,recNo=(select min(recNo) from #t where name=a.name)+(select count(*) from #t where name=a.name and id<=a.id)-1 from #t a order by Name,iddrop table #t猜呗,错了不管哈,哈哈,哈哈哈......
结果应该是:Name recNo ----------------- a 1 a 2 a 3 b 2 b 3 c 4 c 5 c 6 c 7 d 4 e 5 e 6 f 6 f 7 g 7 g 8 g 9不过错了不管哈,哈哈,哈哈哈......
select Name,(select count(distinct Name) from t where Name<=a.Name) recNo from t a order by Name
to: zjcxc(邹建不好意思我没说清楚: 原表中RECNO字段为空,刚才举的例子是返回结果!
select IDENTITY(int, 1,1) AS ID_Num ,* into #t from ( select distinct name from table1) a select * from #t update table1 set recNo=#t.ID_Num from table1 inner join #t on #t.name=table1.name
into #t from 表
order by Nametruncate table 表insert 表
select Name,recNo=(select min(recNo) from #t where name=a.name)+(select count(*) from #t where name=a.name and id<=a.id)-1
from #t a
order by Name,iddrop table #t猜呗,错了不管哈,哈哈,哈哈哈......
-----------------
a 1
a 2
a 3
b 2
b 3
c 4
c 5
c 6
c 7
d 4
e 5
e 6
f 6
f 7
g 7
g 8
g 9不过错了不管哈,哈哈,哈哈哈......
from t a order by Name
原表中RECNO字段为空,刚才举的例子是返回结果!
select * from #t
update table1 set recNo=#t.ID_Num from table1 inner join #t on #t.name=table1.name
真乃牛人也!!!不过当字段name为其它类型时(需要自己加order by)时怎么办呢?加在哪里??谢!
select Name,(select count(distinct Name) from t where Name<=a.Name) recNo
from t a order by Name
因为原始表中的recno为空.
我的原始表中RECNO字段是空的,你的结果不对啊~~~~~~~
set recNo=(select count(distinct Name) from t where Name<=a.Name)pbsql(风云) 都送你到家门口了,还要问“我用左脚还是右脚跨进去”?哈,哈哈,哈哈哈......
还是报错,提示:列前缀'a'与查询中所用的表名或别名不匹配
set recNo=(select count(distinct Name) from t a where a.Name<=t.Name)