首页 新闻 搜索 专区 学院

请问一个关于主从表联合高效查询的SQL问题

0
悬赏园豆:5 [已解决问题] 解决于 2010-09-16 09:39

A表(主表 一端)

有 ID Title Content Sender等内容

B表(从表 多端)

有ID AID(A表ID)Reciver内容

 

我希望出这样的结果

ID Title Content Sender Recivers

比如

1 ‘我要提问’ ‘问题XXXXXX’ ‘发送者’ ‘接收者1,接收者2,接收者3.....’

请问这样的SQL怎么查出来,而且相对高效

Jeremy Ding的主页 Jeremy Ding | 初学一级 | 园豆:0
提问于:2010-09-15 22:32
< >
分享
最佳答案
0

-- oracle 依靠 connect by 很容易就做到了
--
测试数据:
with A as (
select 1 id , 'aaa' title,'******' content,'sender1' sender from dual union all
select 2 id , 'bbb' title,'******' content,'sender2' sender from dual union all
select 3 id , 'ccc' title,'******' content,'sender3' sender from dual
)
, B
as (
select 1 id , 1 aid, 'R01' Reciver,'********' content from dual union all
select 2 id , 1 aid, 'R02' Reciver,'********' content from dual union all
select 3 id , 1 aid, 'R03' Reciver,'********' content from dual union all
select 4 id , 2 aid, 'R04' Reciver,'********' content from dual union all
select 5 id , 2 aid, 'R05' Reciver,'********' content from dual
)
-- >> start here
, nb as (
select aid,ltrim(sys_connect_by_path(Reciver,','),',') line
from
(
select row_number() over(partition by aid order by id) rn,id,aid,reciver from B ) b
where connect_by_isleaf=1
start
with b.rn=1
connect
by prior b.aid=b.aid
and prior b.rn+1=b.rn
)
select a.id,a.title,a.content,a.sender,nb.line recivers
from A inner join nb
on A.id=nb.aid
-- >> end here
--
>> 结果:
ID TIT CONTEN SENDER RECIVERS
---------- --- ------ ------- ------------------------------
1 aaa ****** sender1 R01,R02,R03
2 bbb ****** sender2 R04,R05

-- SQL Server 2005 或以上版本可以借助递归CTE:
--
测试数据:
with A(id,title,content,sender) as (
select 1 id , 'aaa' title,'******' content,'sender1' sender union all
select 2 id , 'bbb' title,'******' content,'sender2' sender union all
select 3 id , 'ccc' title,'******' content,'sender3' sender
)
, B(id,aid,reciver,content)
as (
select 1 id , 1 aid, 'R01' Reciver,'********' content union all
select 2 id , 1 aid, 'R02' Reciver,'********' content union all
select 3 id , 1 aid, 'R03' Reciver,'********' content union all
select 4 id , 2 aid, 'R04' Reciver,'********' content union all
select 5 id , 2 aid, 'R05' Reciver,'********' content
)
-- >> start here
, B_RN_CN(rn,cn,id,aid,reciver) as (
select
row_number()
over(partition by aid order by id) rn,
COUNT(*) over(partition by aid) cn,
id,aid,reciver
from B
)
, cte
as (
select rn,cn,id,aid,reciver,cast(reciver as varchar) line from B_RN_CN where rn=1
union all
select b.rn,b.cn,b.id,b.aid,b.reciver,cast ( line+','+b.reciver as varchar)
from cte c,B_RN_CN b where c.aid=b.aid and b.rn=c.rn+1
)
select a.id,a.title,a.content,a.sender,c.line recivers
from cte c,A
where c.rn=c.cn
and c.aid=a.id
-- end here
--
结果:
id title content sender recivers
----------- ----- ------- ------- ------------------------------
1 aaa ****** sender1 R01,R02,R03
2 bbb ****** sender2 R04,R05

 

收获园豆:5
killkill | 小虾三级 |园豆:992 | 2010-09-15 23:39
感谢
Jeremy Ding | 园豆:0 (初学一级) | 2010-09-16 09:39
其他回答(1)
0

看的云里雾里,你的要求是不是这样的,A表中一个AID对表B中多个项是吧,查询时候根据AID得到B中的多项内容,你的要求是将查询后的结果转换成行,这个在SQLserver 2005中有pivot函数

来自非洲大草原的食人虎 | 园豆:76 (初学一级) | 2010-09-16 00:11
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册