什么是inline view?

其實(shí)很簡(jiǎn)單.?? inline view 就是指 from 后面出現(xiàn)另1個(gè)select 語(yǔ)句.

例如最簡(jiǎn)單的inline view 用法

select table_name from (select * from user_tables)
當(dāng)然上面的inline view用法很多余

讓我們看看下面的例子:

19.View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. The statement would execute successfully to produce the required output.
B. The statement would not execute because inline views and outer joins cannot be used together.
C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.

我們來(lái)做個(gè)實(shí)踐

首先建表和插入數(shù)據(jù)

create table products(product_id number(4),product_name varchar2(20));create table order_items(order_id number(4),product_id number(4),qty number(4),unit_price number(6));insert into products select 1, 'Inkjecet C/8/HQ' from dual; insert into products select 2, 'CPU D300' from dual; insert into products select 3, 'HD 8GB/I' from dual; insert into products select 4, 'HD 12GB/R' from dual; commit;insert into order_items select 11,1,10,100 from dual; insert into order_items select 22,2,15,120 from dual; insert into order_items select 33,3,10,50 from dual; insert into order_items select 44,1,5,10 from dual; insert into order_items select 66,2,20,125 from dual; commit;

在分析下題目中的select 語(yǔ)句:

SQL> select product_id, count(1) item_cnt from order_items group by product_id;PRODUCT_ID ITEM_CNT ---------- ----------1 22 23 1SQL>

select p.product_name, i.item_cnt
from? (select product_id, count(1) item_cnt
??????? from?? order_items
??????? group by product_id) i right outer join products p
????????????????????????????????????????????? on?? p.product_id = i.product_id

可見(jiàn) 紅色的select 語(yǔ)句部分被作為1個(gè)別稱(chēng)為 i 的表 與? products 表 右連接.

紅色部分的執(zhí)行結(jié)果:

SQL> select product_id, count(1) item_cnt from order_items group by product_id;PRODUCT_ID ITEM_CNT ---------- ----------1 22 23 1SQL>

表 products的數(shù)據(jù)

PRODUCT_ID PRODUCT_NAME ---------- --------------------1 Inkjecet C/8/HQ2 CPU D3003 HD 8GB/I4 HD 12GB/RSQL>

所以右連接后. (右邊的數(shù)據(jù)無(wú)對(duì)應(yīng)左邊數(shù)據(jù)的話顯示右邊的數(shù)據(jù))

就成為:

PRODUCT_NAME ITEM_CNT -------------------- ---------- Inkjecet C/8/HQ 2 CPU D300 2 HD 8GB/I 1 HD 12GB/R

所以題目的select 語(yǔ)句是能正確執(zhí)行的.

答案是A

總結(jié)

以上是生活随笔為你收集整理的Oracle inline view 简介的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。