求一条SQL命令的写法

[quanjishou]

请教这样的一条命令如何写，非常感谢 ！

原始表：
上级编号	下级编号
a	001
a	003
b	008
b	123


生成目标表：

编号1	编号2
01	a
0101	001
0102	003
02	b
0201	008
0202	123

[simonxt]

第一种，复杂点

create table #tb (aa varchar(50), bb varchar(50))
insert into #tb(aa,bb) values('a', '001');
insert into #tb(aa,bb) values('a', '003');
insert into #tb(aa,bb) values('b', '008');
insert into #tb(aa,bb) values('b', '123');
---------------
select 
cc = right('00'+rtrim(ROW_NUMBER() over(order by aa)),2), 
dd = aa
from #tb
group by aa
union all
select 
cc=right('00'+rtrim(t2.c1),2)+ right('00'+RTRIM(ROW_NUMBER() over(partition by t1.aa order by t1.aa, t1.bb)),2),
dd=t1.bb
from #tb t1
left join (
	select 
	c1 = ROW_NUMBER() over(order by aa), 
	aa
	from #tb
	group by aa
) t2 on t2.aa=t1.aa
order by cc

第二种 如果是多级结构，还是应该用with来实现

[needanothercoder]

不错，我也是这个意思

[littleasp3]

create table customer(id int identity primary key, name varchar(20) , address varchar(50) )
create table orders(id int identity primary key , order_date datetime , staff int references staff(id)  , customer int references customer(id))
create table product(id int identity primary key , name varchar(20) , price money)
create table orders_product(orders int references orders(id) , product int references product(id) , num int  ,primary key (orders , product))
insert into customer values('家乐福' , '武胜路'); 
insert into customer values('沃尔玛' , '江汉路');
insert into customer values('好又多' , '车站路');
insert into customer values('武商量贩' , '复兴村');
insert into customer values('中百仓储' , '唐家墩');
insert into product values('德芙巧克力' , 6.00); 

insert into product values('百事可乐2.5L' ,7.50);
insert into product values('金龙鱼调和油' , 52.80);
insert into product values('奥妙洗衣服1.5kg' , 16.90 );
insert into product values('海飞丝200ml' , 15.80);

insert into orders values('2010-09-28' , 3 ,5 );
insert into orders_product values(1,1,15); 
insert into orders_product values(1,2,30);
insert into orders_product values(1,3,15);
insert into orders_product values(1,5,10);
select c.name,sum(cc.total) jie
from customer c left jion
  (select p.price*op.num as total,p.name,o.customer
from product p inner jion orders_product op
on p.id=op.product inner jion orders o
on o.id=op.orders)cc
on cc.customer=c.id
group by c.name

[勿勿]

结贴！！！

[JayPan2008]

根据2L稍微优化了一下

SELECT RIGHT('00'+RTRIM(ROW_NUMBER() OVER (ORDER BY PID)), 2) AS PRowNo,PID AS ID
FROM tb
GROUP BY PID
UNION ALL
SELECT P.PRowNo+S.RowNo AS PRowNo,S.ID
FROM 
(
	SELECT RIGHT('00'+RTRIM(ROW_NUMBER() OVER (PARTITION BY PID ORDER BY ID)), 2) AS RowNo,PID,ID 
	FROM tb
) AS S
INNER JOIN 
(
	SELECT RIGHT('00'+RTRIM(ROW_NUMBER() OVER (ORDER BY PID)), 2) AS PRowNo,PID AS ID
	FROM tb
	GROUP BY PID
) AS P
ON S.PID=P.ID
ORDER BY PRowNo

以上代码执行信息
表 'tb'。扫描计数 3，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

2L执行信息如下
表 'tb'。扫描计数 4，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

优化后相比较少执行一次表扫描

[JayPan2008]

IF OBJECT_ID('tb') IS NOT NULL
	DROP TABLE tb
GO
CREATE TABLE tb(
PID VARCHAR(50),
ID VARCHAR(50)
)

INSERT INTO tb(PID, ID) VALUES('a','001')
INSERT INTO tb(PID, ID) VALUES('a','003')
INSERT INTO tb(PID, ID) VALUES('b','008')
INSERT INTO tb(PID, ID) VALUES('b','123')

[mayuanf]

create table #tb (aa varchar(50), bb varchar(50))
insert into #tb(aa,bb) values('a', '001');
insert into #tb(aa,bb) values('a', '003');
insert into #tb(aa,bb) values('b', '008');
insert into #tb(aa,bb) values('b', '123');

with cte as (
select ROW_NUMBER() Over(Order by aa) rw,aa from #tb
group by aa
union all
select rw*100 + ROW_NUMBER() Over(Order by bb), #tb.bb  from #tb
inner join cte on #tb.aa = cte.aa
)
select '0' + cast(rw as varchar) id, aa from cte
order by id

/* result
id	aa
01	a
0101	001
0102	003
02	b
0201	008
0202	123

*/

[chkaka]

[Quote=引用 6 楼 的回复:]

SQL code


create table #tb (aa varchar(50), bb varchar(50))
insert into #tb(aa,bb) values('a', '001');
insert into #tb(aa,bb) values('a', '003');
insert into #tb(aa,bb) values('b', '008');
insert i……
[/Quote]
可以结贴了

[rfq]

go
create table #tb (aa varchar(50), bb varchar(50))
insert into #tb(aa,bb) values('a', '001');
insert into #tb(aa,bb) values('a', '003');
insert into #tb(aa,bb) values('b', '008');
insert into #tb(aa,bb) values('b', '123');
;with mm
as
(
select aa,right('00'+cast(ROW_NUMBER() over(order by aa) as nvarchar),2) as cc,'' as bb from #tb group by aa
)
select * from mm
union
select A.aa,A.cc+B.cc as cc ,A.bb from
(select aa, right('00'+cast(ROW_NUMBER() over(partition by aa order by aa) as nvarchar),2) as cc,bb from #tb) A
inner join
(select * from mm ) B
on A.aa=B.aa
order by cc