求一存储过程，请各位老大帮忙
表A
id     score
1         777
2         696
3         999
.......
表   B
pid       pname
  1           100
  2             80  
  3             70
  ........
找出select   top   10   id   from   A   order   by   score   desc的记录

select   top   10   pname   from   B   order   by   pname   desc的记录

得出表C
cid       A.id     B.pname     A.score
1             3           100             999
2             1             80             777
3             2             70             696
...................

------解决方案--------------------
挺简单的，自己先动脑，查资料，应该没问题
------解决方案--------------------
同意一楼
------解决方案--------------------
CREATE PROCEDURE au_info_all
AS
select id1 = identity(int,1,1) , * into tb1 from A where id in (select top 10 id from A order by score desc)
select id1 = identity(int,1,1) , * into tb2 from B where name in (select top 10 pname from B order by pname desc)
select a.Pid,b.id,a.pname,b.score from tb2 a , tb1 b where a.id1 = b.id1
drop table tb1,tb2
GO

------解决方案--------------------
借用臨時表

select top 10 *, OrderID = Identity(Int, 1, 1) Into #T1 from A order by score desc

select top 10 *, OrderID = Identity(Int, 1, 1) Into #T2 from B order by pname desc

Select
A.OrderID As cid,
A.id,
B.pname,
A.score
From
#T1 A
Inner Join
#T2 B
On A.OrderID = B.OrderID

Drop Table #T1, #T2