字段值排序加一,高手请进!在线等!

Question

select t.leaseID,t.houseid,t.newid+'-'+(select count(1) from table1 where newid=t.newid and houseid<=t.houseid) from table1 t

Answer 1

SELECT leaseID,houseid,[NEWID]=[NEWID]+LTRIM((SELECT COUNT(*) FROM TABLE1 WHERE [newid]=T.[newid] AND houseid<=T.houseid ))  FROM TABLE1 T

Answer 2

sql2005下用row_mumber就可以了。select *,[newid]=[newid] + rtrim(row_number() over(partition by leaseid order by houseid)) from tb没有测试环境，随手敲的。

Answer 3

SELECT leaseID,houseid,[NEWID]=[NEWID]+'-'+LTRIM((SELECT COUNT(*) FROM TABLE1 WHERE [newid]=T.[newid] AND houseid<=T.houseid ))  FROM TABLE1 T

Answer 4

declare @t1 table(leaseID varchar(16),houseid varchar(16),newid varchar(16))
insert into @t1 select 'S18118090707   ','18-118','SY20080708'
insert into @t1 select 'S18225090707   ','18-225','SY20080708'
insert into @t1 select 'S18226090707   ','18-226','SY20080708'
insert into @t1 select 'S18228232090707','18-228','SY20080507'
insert into @t1 select 'S18229090707   ','18-229','SY20080601'select 
    t.leaseID,
    t.houseid,
    t.newid+'-'+rtrim((select count(1) from @t1 where newid=t.newid and houseid<=t.houseid)) as newid
from 
    @t1 t/*
leaseID          houseid          newid                         
---------------- ---------------- ----------------------------- 
S18118090707     18-118           SY20080708-1
S18225090707     18-225           SY20080708-2
S18226090707     18-226           SY20080708-3
S18228232090707  18-228           SY20080507-1
S18229090707     18-229           SY20080601-1
*/

Answer 5

create table tb(leaseID varchar(20) , houseid varchar(20) , [newid] varchar(20))
insert into tb values('S18118090707' ,'18-118', 'SY20080708') 
insert into tb values('S18225090707' ,'18-225', 'SY20080708') 
insert into tb values('S18226090707' ,'18-226', 'SY20080708') 
insert into tb values('S18228232090707' ,'18-228', 'SY20080507') 
insert into tb values('S18229090707' ,'18-229', 'SY20080601')
goselect leaseid , houseid ,[newid] = [newid] + '-' + cast((select count(1) from tb where [newid] = t.[newid] and houseid < t.houseid) + 1 as varchar) from tb tdrop table tb /*
leaseid              houseid              newid                                               
-------------------- -------------------- --------------------------------------------------- 
S18118090707         18-118               SY20080708-1
S18225090707         18-225               SY20080708-2
S18226090707         18-226               SY20080708-3
S18228232090707      18-228               SY20080507-1
S18229090707         18-229               SY20080601-1（所影响的行数为 5 行）
*/

Answer 6

create table test(leaseID varchar(50),houseid varchar(50),[newid] varchar(20))
go
           
insert test select 'S18118090707', '18-118', 'SY20080708' 
insert test select 'S18225090707','18-225', 'SY20080708' 
insert test select 'S18226090707', '18-226', 'SY20080708' 
insert test select 'S18228232090707', '18-228', 'SY20080507' 
insert test select 'S18229090707' ,'18-229', 'SY20080601' 
go
select a.leaseID,a.houseid,
a.[newid]+'-'+ltrim((select count(1) from test where [newid]=a.[newid] and houseid<=a.houseid))
from test a
/*
leaseID                                            houseid                                                                              
-------------------------------------------------- -------------------------------------------------- --------------------------------- 
S18118090707                                       18-118                                             SY20080708-1
S18225090707                                       18-225                                             SY20080708-2
S18226090707                                       18-226                                             SY20080708-3
S18228232090707                                    18-228                                             SY20080507-1
S18229090707                                       18-229                                             SY20080601-1（所影响的行数为 5 行）
*/
drop table test