以下兩個查詢給出了相同的結果，只是順序不同。
select pack_id ,speed , monthly_payment
from acdb_packages
where monthly_payment > all
( select monthly_payment from acdb_packages where speed = '5Mbps' );
select p1.pack_id, p1.speed, p1.monthly_payment
from acdb_packages p1
where p1.monthly_payment >
( select max(monthly_payment) from acdb_packages p2 where p2.speed ='5Mbps' );
為了通過使用 max 而不是 ALL 來更改第一個查詢，我撰寫了第二個查詢。結果顯示它們都具有相同的行數。但不同的是順序。
結果顯示第一個的順序是monthly_payment asc，第二個的順序是pact_id asc。第二個查詢有什么問題嗎？我也想要同樣的順序。
uj5u.com熱心網友回復：
如果你想要一個有序的結果，你需要添加一個ORDER BY子句。否則，您的結果將采用“隨機”排序順序。有一些規則可以對結果進行自動排序，但是如果您沒有指定要排序的結果，則希望 sql 引擎對事物進行排序是不好的做法。
因此，您可以在末尾添加，ORDER BY 3這是按第三個結果列排序結果的縮寫，或者更好地添加ORDER BY monthly_payment
uj5u.com熱心網友回復：
如果您希望查詢結果以特定順序顯示，則需要使用 ORDER BY 子句。關系 DBMS 沒有內置的記錄順序，并且記錄的順序是隨機的——它們通常以 PK 順序回傳，但不應假定或依賴這一點
uj5u.com熱心網友回復：
在我的測驗中，> all版本使用了全掃描，所以回傳的結果是按照它們在存盤中碰巧找到的順序回傳的，而= max版本使用了我在 上創建的索引monthly_payment，所以按照索引回傳的順序回傳結果——驅動的嵌套回圈操作，表示 的值monthly_payment在索引中保存的順序。
create table acdb_packages
( pack_id number constraint acdb_packages_pk primary key
, monthly_payment number not null
, speed varchar2(10) not null );
insert all
into acdb_packages (pack_id, monthly_payment, speed) values ( 1, 90, '200Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 2, 80, '175Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 3, 70, '150Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 4, 60, '100Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 5, 70, '10Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 6, 60, '5Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 7, 50, '4Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 8, 70, '20Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values ( 9, 60, '5Mbps')
into acdb_packages (pack_id, monthly_payment, speed) values (10, 50, '4Mbps')
select * from dual;
create index acdb_packages_monthly_payment_ix on acdb_packages (monthly_payment);
第一個查詢：
select pack_id, speed, monthly_payment
from acdb_packages
where monthly_payment > all
( select monthly_payment from acdb_packages where speed = '5Mbps' );
PACK_ID SPEED MONTHLY_PAYMENT
---------- ---------- ---------------
1 200Mbps 90
2 175Mbps 80
3 150Mbps 70
5 10Mbps 70
8 20Mbps 70
5 rows selected.
Plan hash value: 2904906835
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 1 | 23 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS STORAGE FULL| ACDB_PACKAGES | 10 | 130 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL| ACDB_PACKAGES | 2 | 20 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage("SPEED"='5Mbps' AND "MONTHLY_PAYMENT"<="MONTHLY_PAYMENT")
filter("SPEED"='5Mbps' AND "MONTHLY_PAYMENT"<="MONTHLY_PAYMENT")
第二個查詢：
select pack_id, speed, monthly_payment
from acdb_packages
where monthly_payment >
( select max(monthly_payment) from acdb_packages p2 where p2.speed ='5Mbps' );
PACK_ID SPEED MONTHLY_PAYMENT
---------- ---------- ---------------
3 150Mbps 70
5 10Mbps 70
8 20Mbps 70
1 200Mbps 90
2 175Mbps 80
5 rows selected.
Plan hash value: 1313930309
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| ACDB_PACKAGES | 1 | 13 | 1 (0)| 00:00:01 |
| 2 | SORT CLUSTER BY ROWID BATCHED | | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACDB_PACKAGES_MONTHLY_PAYMENT_IX | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 10 | | |
|* 5 | TABLE ACCESS STORAGE FULL | ACDB_PACKAGES | 2 | 20 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P1"."MONTHLY_PAYMENT"> (SELECT MAX("MONTHLY_PAYMENT") FROM "ACDB_PACKAGES" "P2" WHERE
"P2"."SPEED"='5Mbps'))
5 - storage("P2"."SPEED"='5Mbps')
filter("P2"."SPEED"='5Mbps')
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/457836.html
上一篇：SQL合并兩個表中的資料
下一篇：截斷表Dataverse