作者artarmon (Downunder)
看板Database
标题Re: [讨论] 使用子查询的时机(已补上实际例子)
时间Wed Jun 11 19:26:43 2014
※ 引述《bohei (run and fall)》之铭言：
: 从写SQL以来,常遇到某些状况需要使用到子查询
: 但在用子查询时又很怕拖累效能
: 常常在想有没有其他的解法
: 我想请问大家的是
: 在某些情况下是不是一定只有子查询的解法
: 不需再考虑其他写法了?
: ex:最近的例子,需先GROUP BY後比较某栏位的和,符合的才捞出,
: 但最後要的结果是这些符合的和的总和,
: 在想法上需要用两个SELECT去达成(第一个先GROUP BY比较,第二个去捞全部的总和)
: 这种例子是不是只能使用子查询了?
: 先谢谢大家的解答!
: 补上简化後的例子:
: 项次 数量
: 1 10
: 1 20
: 2 40
: 3 30
: 3 30
: 要找出各项次"数量和" <50 的 "所有数量"
: 第一步先以项次分群,分别是
: 项次 数量
: 1 30
: 2 40
: 3 60
: 应捞出项次1、2
: 故得出总和为30+40=70
建议使用 CTE(common table expression)取代 sub query, 以下是范例
create table inventory (
item int,
qty int)
insert into inventory values(1,10)
insert into inventory values(1,20)
insert into inventory values(2,40)
insert into inventory values(3,30)
insert into inventory values(3,30)
with subtotal as (
select item, sum(qty) as total
from inventory
group by item
having sum(qty) < 50
)
select sum(total)
from subtotal
CTE 是 ANSI-SQL 99的标准语法，各大资料库都有支援，在撰写复杂的 query时
非常好用，尤其是需要建立 hierarchy的时候
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 60.240.26.143
※ 文章网址: http://webptt.com/cn.aspx?n=bbs/Database/M.1402486006.A.245.html
1F：推 bohei:明天到公司马上试试！谢谢！ 06/11 23:47
2F：→ bohei:HI,你好,我在oracle 9i试可work,但我上google看了一下CTE与 06/12 09:53
3F：→ bohei:sub-query的差别,CTE可读性较好,但似乎没有效能的差别? 06/12 09:54
4F：→ bohei:不知我的理解有没有错误~ 还请A大指教!谢谢! 06/12 09:54
5F：→ artarmon:CTE可以做到subquery做不到的功能，例如recursive query, 06/12 10:48
6F：→ artarmon: 至於效能，不同资料库实作方式不一样, 有的会针对CTE做 06/12 10:48
7F：→ artarmon:优化 06/12 10:48
8F：→ artarmon:CTE适合当driving list用，减少查询资料量 06/12 10:56
9F：推 bohei:不好意思再请教一下..今天跟google做了一点功课, 06/12 16:31
10F：→ bohei:recursive CTE与Oracle提供的connect by好像是相似的, 06/12 16:38
11F：→ bohei:不知道我有没有理解错误..再请A大开示一下..谢谢 06/12 16:38
12F：→ bohei:另外就是你提到的当作driving list 减少资料查询量, 06/12 17:34
13F：→ bohei:这用法又是相同於TEMP TABLE的使用吗? 06/12 17:34
14F：推 scpisces:CTE相似於TEMP TABLE，但还是有限制 06/12 20:15
15F：→ scpisces:网路上有不少说明 06/12 20:16
16F：→ artarmon:在推文里很难讲清楚，connect by是Oracle专用, not ansi 06/12 20:51
17F：→ artarmon:CTE可以当temp table用,但是不能加index 06/12 20:53
18F：→ bohei:好的！那我理解的方向应该没错！ 06/12 23:59
19F：→ bohei:感谢两位大大的说明,又上了一课！ 06/13 00:00