Posted on 2010-12-15 13:22
pts 閱讀(408)
評論(0) 編輯 收藏
From：http://database.51cto.com/art/201010/231773.htm
oracle多列子查詢是oracle數據庫中的一種查詢方式，下面就為您詳細介紹oracle多列子查詢的實現方法，希望能夠對您能夠有所幫助。
多列子查詢：
oracle子查詢一般都是返回單列,實際上數據庫對此并沒有進行限制,子查詢也可以返回多列.oracle多列子查詢的例子:
檢索每種產品在其類型中價格最低的產品:
步驟:
1.通過分組查詢,統計每類產品的最低價格:
<ol class="dp-xml" style="margin-top: 0px !important; margin-right: 0px !important; margin-bottom: 1px !important; margin-left: 3em !important; padding-top: 5px; padding-right: 0px; padding-bottom: 5px; padding-left: 20px; list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-image: url(http://www.51cto.com/images/art/images/010101bg.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; color: #000000; line-height: 30px; background-position: initial initial; background-repeat: no-repeat repeat; border: 0px none initial;"><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">select product_type_id,min(Price) from products group by product_type_id </span></span></li></ol>
結果為:
PRODUCT_TYPE_ID MIN(PRICE)
--------------- ----------
1 19.95
2 13.95
3 12.99
4 10.99
13.49
2. 然后再掃描產品表中每行數據,看當前行的類別編輯與價格是否屬于上面子查詢中的一項;
完整查詢:
<ol class="dp-xml" style="margin-top: 0px !important; margin-right: 0px !important; margin-bottom: 1px !important; margin-left: 3em !important; padding-top: 5px; padding-right: 0px; padding-bottom: 5px; padding-left: 20px; list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-image: url(http://www.51cto.com/images/art/images/010101bg.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; color: #000000; line-height: 30px; background-position: initial initial; background-repeat: no-repeat repeat; border: 0px none initial;"><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">select product_id,product_type_id,name,price </span></span></li><li style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: #000000; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">from products </span></li><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">where </span></li><li style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: #000000; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">(product_type_id , price) </span></li><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">in </span></li><li style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: #000000; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">(select product_type_id , min(price) </span></li><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">from </span></li><li style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: #000000; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">products </span></li><li class="alt" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: inherit; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">group by </span></li><li style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 12px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 14px; list-style-type: decimal; list-style-position: outside !important; list-style-image: initial; color: #000000; line-height: 24px; background-image: url(http://www.51cto.com/images/art/AppData/Roaming/Adobe/Dreamweaver%20CS4/zh_CN/Configuration/images/dt1.gif); background-attachment: initial; background-origin: initial; background-clip: initial; background-color: initial; background-position: initial initial; background-repeat: no-repeat no-repeat; border: 0px none initial;"><span style="color: black; background-color: inherit; padding: 0px; margin: 0px; border: 0px none initial;">product_type_id) </span></li></ol>
查詢結果:
PRODUCT_ID PRODUCT_TYPE_ID NAME PRICE
---------- --------------- ------------------------------ ----------
1 1 Modern Science 19.95
4 2 Tank War 13.95
8 3 From Another Planet 12.99
9 4 Classical Music 10.99