首页新闻找找看学习计划

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.还有没有其他的方案呢?

Gamain的主页 Gamain | 菜鸟二级 | 园豆:357
提问于:2013-08-19 11:27
< >
分享
最佳答案
0


select A.年级 AS 年级,
AVG (decode(class,'语文成绩',score,0))
AVG (decode(class,'数学成绩',score,0))
from  A,B
group by A.年级
具体怎么用,自己查查吧。

收获园豆:5
kylin.chen | 小虾三级 |园豆:983 | 2013-08-19 11:34

非常感谢,用你的方式实现了我想要的功能。而且学习到了decode函数的用法。

Gamain | 园豆:357 (菜鸟二级) | 2013-08-19 15:29
其他回答(2)
0

不推荐楼上的方案,这个写法有点过时了。

建议直接连表查询(不是你的第二种连表)。然后行转列即可。

select * from A

left join B

on A.c=B.c

group by A.c1,B.c1类似的写法

收获园豆:2
幻天芒 | 园豆:36522 (高人七级) | 2013-08-19 11:42

我这个过时了??!!
行列转换效率很差的,oracle的行列转换函数我没用过(不清楚有没有),不过Sql server 2008的转换效率很差。

支持(0) 反对(0) kylin.chen | 园豆:983 (小虾三级) | 2013-08-19 11:45

这种行列转换估计跟临时表差不多,这种decode的写法效率绝对不差。

支持(0) 反对(0) kylin.chen | 园豆:983 (小虾三级) | 2013-08-19 11:47

注意:计算平均值的时候,group by a_id就可以了,然后用计算后的结果去Left join年级表获取名称就可以了。

支持(0) 反对(0) kylin.chen | 园豆:983 (小虾三级) | 2013-08-19 11:51

@kylin.chen: 我是说直接From A,B这种写法,这个写法在最新的SQL标准中,不被推荐。行列转换效率是比较差,不过对于汇总后的数据来说,这个也还是可以用的~。

表示又认识了decode这样的写法,感谢~

支持(0) 反对(0) 幻天芒 | 园豆:36522 (高人七级) | 2013-08-19 12:59

@幻天芒: Oracle对sql语句会进行优化的,这是Oracle的强项,我不是DBA,具体怎么优化我不知道。我相信sql server的优化也有,这种From a,b的方式也肯定给你优化的,我曾经在bom中就这样写过,数据量超过百万了,而且还递归,没有发现速度慢。

我相信From a,b这种写法肯定会被转化为from a inner join b这种写法,而且oracle中的(+)写法也不是标准Sql,尤其对标准Sql每个数据库都会转化为自己的特有写法的,对数据库来说,他们自己设计的产品,自己的写法肯定效率最高。

支持(0) 反对(0) kylin.chen | 园豆:983 (小虾三级) | 2013-08-19 13:37

@kylin.chen: 正如你所说,是会被转换为inner join这种...。直接写成这样不就行了...

话说还是数据量大才有优化的余地~

支持(0) 反对(0) 幻天芒 | 园豆:36522 (高人七级) | 2013-08-19 14:08

然后行转列即可。

这个我不太理解,难道是查询出来然后再做行列转换吗,那岂不是要两步操作?

支持(0) 反对(0) Gamain | 园豆:357 (菜鸟二级) | 2013-08-19 15:31

@Gamain: 查询和行列转换可以是一条语句,虽然是两步~

支持(0) 反对(0) 幻天芒 | 园豆:36522 (高人七级) | 2013-08-19 17:31
0

过时??Oracle有公佈說過時麼?

用case when也行,你的兩種方案在數據量百萬級別(笛卡爾積)以下基本不會有差別,正確的優化是關鍵,性別列用位圖索引,不允許空值,A B表的關聯字段用聚簇索引,年级用散列索引 升序排序,如果你的数据量很大单A表就成百上千万记录那么按照年纪字段可以进行分区索引和存储,真正的谁快谁慢 你自己必须建表用测试数据说话

完了之后你可以看看执行计划,首先要对A表全表扫描,这是你的基本表,然后计算“男生语文平均成绩”时 聚簇索引和位图索引扫描(这个速度很快的)

收获园豆:3
Sam G. | 园豆:218 (菜鸟二级) | 2013-08-19 13:17

非常感谢你的回复,学习到很多知识。

支持(0) 反对(0) Gamain | 园豆:357 (菜鸟二级) | 2013-08-19 15:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册