海上月明

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