求一条SQL命令的写法

quanjishou 2012-04-04 10:44:07


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

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


生成目标表:

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



...全文
156 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
rfq 2012-04-06
  • 打赏
  • 举报
回复
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

chkaka 2012-04-05
  • 打赏
  • 举报
回复
[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]
可以结贴了
mayuanf 2012-04-05
  • 打赏
  • 举报
回复


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

*/


JayPan2008 2012-04-05
  • 打赏
  • 举报
回复

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')

JayPan2008 2012-04-05
  • 打赏
  • 举报
回复
根据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 次。

优化后相比较少执行一次表扫描
勿勿 2012-04-05
  • 打赏
  • 举报
回复
结贴!!!
littleasp3 2012-04-05
  • 打赏
  • 举报
回复

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


needanothercoder 2012-04-05
  • 打赏
  • 举报
回复
不错,我也是这个意思
simonxt 2012-04-04
  • 打赏
  • 举报
回复
第一种,复杂点

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来实现

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧