高手帮忙一下,应该不是很难的
有一个表,如下:
name code
aa 01
bb 01
cc 02
dd 02
ee 02
现在想实现这样的结果
name exp1 code
aa 01 01
bb 0101 01
cc 02 02
dd 0201 02
ee 0202 02
问题一解决马上结贴.
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(name varchar(10),code varchar(10))
insert into tb(name,code) values( 'aa ', '01 ')
insert into tb(name,code) values( 'bb ', '01 ')
insert into tb(name,code) values( 'cc ', '02 ')
insert into tb(name,code) values( 'dd ', '02 ')
insert into tb(name,code) values( 'ee ', '02 ')
go
select name , code exp1 , code from
(
select px=(select count(1) from tb where code=a.code and name <a.name)+1 , * from tb a
) t where px = 1
union all
select name , exp1 = code + right( '00 ' + cast((px-1) as varchar),2),code from
(
select px=(select count(1) from tb where code=a.code and name <a.name)+1 , * from tb a
) t where px <> 1
order by exp1
drop table tb
/*
name exp1 code
---------- -------------- ----------
aa 01 01
bb 0101 01
cc 02 02
dd 0201 02
ee 0202 02
（所影响的行数为 5 行）
*/
[解决办法]
drop table #i
drop table #test
create table #test (name varchar(20),code varchar(20))
insert into #test
select 'aa ', '01 ' union all
select 'bb ', '01 ' union all
select 'cc ', '02 ' union all
select 'dd ', '02 ' union all
select 'ee ', '02 '
--select * from #test
select *,id=identity(int,1,1) into #i from #test
--select * from #i
select * ,replace(code+ '0 '+(select str(count(1)) from #i where a.code=code and a.id> id ), ' ', ' ') from #i a
我只想到这样的~
[解决办法]
create table tst1
(
name nvarchar(2),
code nvarchar(2)
)
insert tst1
select 'aa ', '01 ' union all
select 'bb ', '01 ' union all
select 'cc ' , '02 'union all
select 'dd ', '02 ' union all
select 'ee ' , '02 'union all
select 'ff ', '02 ' union all
select 'gg ', '02 '
go
select a.name,[exp1] = a.code + ( case(right(count(1)+99 ,2)) when '00 ' then ' ' else right(count(1)+99 ,2) end), a.code
from tst1 a,tst1 b
where a.code = b.code and a.name > = b.name
group by a.name,a.code
------------
name exp1 code
---- ------ ----
aa 01 01
bb 0101 01
cc 02 02
dd 0201 02
ee 0202 02
ff 0203 02
gg 0204 02
（所影响的行数为 7 行）
[解决办法]
CREATE TABLE tb
(
name VARCHAR(5),
code VARCHAR(5)
)
INSERT INTO tb
SELECT 'aa ', '01 ' UNION ALL
SELECT 'bb ', '01 ' UNION ALL
SELECT 'cc ', '02 ' UNION ALL
SELECT 'dd ', '02 ' UNION ALL
SELECT 'ee ', '02 '
go
CREATE TABLE #
(
name VARCHAR(5),
code VARCHAR(5),
ord int identity(1,1)
)
insert into #(name,code) select name,code from tb
select name,code,reverse(cast(reverse(code+right( '00 '+cast((select count(*) from # where code=a.code and ord <a.ord) as varchar),2)) as int)) exp from # a
--结果
name code exp
----- ----- ------------
aa 01 01
bb 01 0101
cc 02 02
dd 02 0201
ee 02 0202
(5 行受影响)
[解决办法]
这么多的帖子！
set nocount on
if object_id( 'tb ') is not null
drop table tb
go
create table tb(name varchar(10),code varchar(10))
insert into tb(name,code) values( 'aa ', '01 ')
insert into tb(name,code) values( 'bb ', '01 ')
insert into tb(name,code) values( 'cc ', '02 ')
insert into tb(name,code) values( 'dd ', '02 ')
insert into tb(name,code) values( 'ee ', '02 ')
insert into tb(name,code) values( 'gg ', '02 ')
insert into tb(name,code) values( 'ff ', '02 ')
go
select name,exp1,code from
(
select name,exp1=code,code,bh=1 from tb a where not exists
(
select 1 from tb where code=a.code and name <a.name
)
union all
select name,exp1=code+ '0 '+cast((select count(1) from tb where code=a.code and name <a.name) as varchar),code,bh=2 from tb a where exists
(
select 1 from tb where code=a.code and name <a.name
)
)b
order by name,bh
drop table tb
/*
name exp1 code
---------- ----------------------------------------- ----------
aa 01 01
bb 0101 01
cc 02 02
dd 0201 02
ee 0202 02
ff 0203 02
gg 0204 02
*/
