Oracle 主子表嵌套查询
0
悬赏园豆：10
[已解决问题]
解决于 2013-08-19 15:28
假设有如下A、B两张表，B是A的子表，通过A_ID关联到A：
A表：
ID 年级(1,2,3,4,5,6)
B表：
ID A_ID 性别(0,1) 语文成绩 数学成绩
现在需要得出下面结构的查询结果
结果
年级 男生语文平均成绩 男生数学平均成绩 女生语文平均成绩 女生语文平均成绩
-----------------------------------
有两种可行方案；
第一种：
select
A.年级 AS 年级,
AVG(select 语文成绩 from B where A_ID=A.ID and 性别=0) AS 男生语文平均成绩,
AVG(select 数学成绩 from B where A_ID=A.ID and 性别=0) AS 男生数学平均成绩,
AVG(select 语文成绩 from B where A_ID=A.ID and 性别=1) AS 女生语文平均成绩,
AVG(select 数学成绩 from B where A_ID=A.ID and 性别=1) AS 女生数学平均成绩
from A
group by 年级
第二种：
select
A.年级 AS 年级,
AVG(T1.语文成绩) AS 男生语文平均成绩,
AVG(T1.数学成绩) AS 男生数学平均成绩,
AVG(T2.语文成绩) AS 女生语文平均成绩,
AVG(T2.数学成绩) AS 女生数学平均成绩
from
A
left join (select B.A_ID,B.语文成绩,B.数学成绩 from B where B.性别=0) as T1
on A.ID=T1.A_ID
left join (select B.A_ID,B.语文成绩,B.数学成绩 from B where B.性别=1) as T2
on A.ID=T2.A_ID
group by 年级
----------------------
我想问的是：
1.这种嵌套查询会不会非常影响效率？(慢点是肯定的，只是不知道对效率影响大不大)
2.第二种效率会比第一种高吗？(只有两个嵌套，第一种有四个嵌套)
3.还有没有其他的方案呢？
最佳答案
0
用
select A.年级 AS 年级,
AVG (decode(class,'语文成绩',score,0))
AVG (decode(class,'数学成绩',score,0))
from A,B
group by A.年级
具体怎么用，自己查查吧。
收获园豆：5
非常感谢，用你的方式实现了我想要的功能。而且学习到了decode函数的用法。
其他回答(2)
0
不推荐楼上的方案，这个写法有点过时了。
建议直接连表查询（不是你的第二种连表）。然后行转列即可。
select * from A
left join B
on A.c=B.c
group by A.c1,B.c1类似的写法
收获园豆：2
我这个过时了？？！！
行列转换效率很差的，oracle的行列转换函数我没用过（不清楚有没有），不过Sql server 2008的转换效率很差。
这种行列转换估计跟临时表差不多，这种decode的写法效率绝对不差。
注意：计算平均值的时候，group by a_id就可以了，然后用计算后的结果去Left join年级表获取名称就可以了。
@kylin.chen: 我是说直接From A,B这种写法，这个写法在最新的SQL标准中，不被推荐。行列转换效率是比较差，不过对于汇总后的数据来说，这个也还是可以用的~。
表示又认识了decode这样的写法，感谢~
@幻天芒: Oracle对sql语句会进行优化的，这是Oracle的强项，我不是DBA，具体怎么优化我不知道。我相信sql server的优化也有，这种From a,b的方式也肯定给你优化的，我曾经在bom中就这样写过，数据量超过百万了，而且还递归，没有发现速度慢。
我相信From a,b这种写法肯定会被转化为from a inner join b这种写法，而且oracle中的（+）写法也不是标准Sql，尤其对标准Sql每个数据库都会转化为自己的特有写法的，对数据库来说，他们自己设计的产品，自己的写法肯定效率最高。
@kylin.chen: 正如你所说，是会被转换为inner join这种...。直接写成这样不就行了...
话说还是数据量大才有优化的余地~
然后行转列即可。
这个我不太理解，难道是查询出来然后再做行列转换吗，那岂不是要两步操作？
@Gamain: 查询和行列转换可以是一条语句，虽然是两步~
0
过时??Oracle有公佈說過時麼？
用case when也行，你的兩種方案在數據量百萬級別（笛卡爾積）以下基本不會有差別，正確的優化是關鍵，性別列用位圖索引，不允許空值，A B表的關聯字段用聚簇索引，年级用散列索引 升序排序，如果你的数据量很大单A表就成百上千万记录那么按照年纪字段可以进行分区索引和存储，真正的谁快谁慢 你自己必须建表用测试数据说话
完了之后你可以看看执行计划，首先要对A表全表扫描，这是你的基本表，然后计算“男生语文平均成绩”时 聚簇索引和位图索引扫描（这个速度很快的）
收获园豆：3
非常感谢你的回复，学习到很多知识。
