日期:2014-05-17 浏览次数:20697 次
select a.mid,XX as serio,b.name from Tmain a, Tchd b where a.mid=b.mid
create table TMain(Mid varchar(10))
insert into TMain
select '001' union all
select '002'
create table TChd
(Mid varchar(10), name varchar(10))
insert into TChd
select '001', 'jack' union all
select '001', 'mary' union all
select '001', 'rose'
select a.Mid,
replicate('0',2-len(b.rn))+rtrim(b.rn) 'serio',
b.name
from TMain a
inner join (select Mid,name,
row_number() over(partition by Mid order by getdate()) 'rn'
from TChd) b on a.Mid=b.Mid
/*
Mid serio name
---------- ---------- ----------
001 01 jack
001 02 mary
001 03 rose
(3 row(s) affected)
*/