求一条超难的sql语句??

Question

create table #A
(
id int identity(1,1),
name varchar(64)
)
insert into #A(name) values('aa')
insert into #A(name) values('bb')
create table #B
(
id int identity(1,1),
parentid int,
name varchar(64),
number int
)
insert into #B(parentid,name,number) values(1,'aa1',34)
insert into #B(parentid,name,number) values(1,'aa2',21)
insert into #B(parentid,name,number) values(2,'bb1',24)
insert into #B(parentid,name,number) values(2,'bb2',11)
想出来的效果
name      number
aa
aa1        34
aa2        21
bb
bb1        24
bb2        11

Answer 1

select name,null from #a union all select name,number from #b order by name  ?

Answer 2

insert into #b(parentid,name)
select id,name from #aselect name,number from #b order by name asc

Answer 3

select name,number=replace(number,0,'')
from 
(select name,number='' from #A
union 
select name,number from #B)a
name                                                             number                                                                                                                                                                                                                                                           
---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
aa                                                               
aa1                                                              34
aa2                                                              21
bb                                                               
bb1                                                              24
bb2                                                              11（所影响的行数为 6 行）

Answer 4

select name,number from
(select id,name,'' number from #A 
union 
select parentid id,name,rtrim(number) number from #B) T
order by id,number,name

Answer 5

create table #A
(
id int identity(1,1),
name varchar(64)
)
insert into #A(name) values('aa')
insert into #A(name) values('bb')
create table #B
(
id int identity(1,1),
parentid int,
name varchar(64),
number int
)
insert into #B(parentid,name,number) values(1,'aa1',34)
insert into #B(parentid,name,number) values(1,'aa2',21)
insert into #B(parentid,name,number) values(2,'bb1',24)
insert into #B(parentid,name,number) values(2,'bb2',11)select name,number = '' from #a
union all
select #b.name , cast(#b.number as varchar(10)) from #b,#a
where #b.parentid = #a.id
order by namedrop table #a
drop table #b--结果
name  number     
----- ---------- 
aa                                                               
aa1   34
aa2   21
bb                                                               
bb1   24
bb2   11（所影响的行数为 6 行）

Answer 6

select c.name,c.number from 
(select name,null as number,id from #a
union
select name,number,parentid as id from #b)c
order by c.id,case when isnull(number,0)=0 then 0 else 1 end