两个表（例如主从表）进行联合查询
例如主表是这样：
表名：TMain
Mid
001
002
从表：TChd
Mid name
001 jack
001 mary
001 rose
……
怎么样通过一个语句得到如下的结果
Mid serio name
001 01 jack
001 02 mary
001 03 rose
……
其中serio是根据数据库默认的顺序给排的号。类似用如下的语句
select a.mid,XX as serio,b.name from Tmain a, Tchd b where a.mid=b.mid
------解决方案--------------------
select a.mid,row_number() OVER(order by a.mid DESC) 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)
*/
------解决方案--------------------
用 row_number() OVER
------解决方案--------------------
select a.mid,row_number() OVER(order by a.mid DESC) as serio,b.name from Tmain a, Tchd b where a.mid=b.mid
还是这个简洁