日期:2014-05-18  浏览次数:20446 次

这个怎么实现?
select   id   from   t1   order   by   id

id
200702
200702
200704
200704
200705

查询结果如上
我想变成如下结果
id
标项1
标项1
标项2
标项2
标项3
......以此类推


------解决方案--------------------
select
'标项 '+cast((select count(distinct id) from t1 where id <=a.id) as varchar) as id
from t1 a
order by id
------解决方案--------------------
select *,con=identity(int,1,1) into #
from 表名A

update 表名A
set id= '标项 '+rtrim(a.con)
from # a where a.id=表名A.id
查询:
select * from 表名A
------解决方案--------------------
declare @t table(id varchar(20))
insert @t select '200702 '
union all select '200702 '
union all select '200704 '
union all select '200704 '
union all select '200705 '

select '标项 '+rtrim((select count(distinct id) from @t where id <=a.id)) from @t a

--结果
----------------
标项1
标项1
标项2
标项2
标项3

(所影响的行数为 5 行)
------解决方案--------------------
create table T(col1 int, col2 varchar(10))
insert T select 123, '200702 '
union all select 123, '200702 '
union all select 123, '200703 '
union all select 123, '200703 '
union all select 123, '200705 '
union all select 123, '200705 '
union all select 123, '200706 '

declare @col1 int, @col2 varchar(10), @col2_old varchar(10), @id int
declare @t table(col1 int, col2 varchar(10))

declare cur cursor local
for
select * from T
open cur

fetch next from cur into @col1, @col2
select @col2_old=@col2, @id=1
while @@fetch_status=0
begin
if @col2_old=@col2
insert @t select @col1, '标项 '+rtrim(@id)
else
begin
select @id=@id+1, @col2_old=@col2
insert @t select @col1, '标项 '+rtrim(@id)
end

fetch next from cur into @col1, @col2
end

close cur
deallocate cur

select * from @t

--result
col1 col2
----------- ----------
123 标项1
123 标项1
123 标项2
123 标项2
123 标项3
123 标项3
123 标项4

(7 row(s) affected)