求 Sql 写法 in oracle， 急！

Question

现有两表，分别为 A,B. 其中　A　有字段　ITEM＿NO，　ITEM＿SUB_NO，其它;　B　有　字段　ITEM＿NO，　其它.
   A 表中相同　ITEM＿NO　数据可能有多条，通过　ITEM＿SUB＿NO　区别;　B　表中相同　ITEM＿NO　数据可能有多条,　通过其它字段区别;　现要求取出　A，B　表中　ITEM＿NO　相同，　并且　A　表中ITEM＿SUB＿NO最大的  所有B表中的记录。　其中A表数据量较大(千万级)，请充分考虑效率问题。
　　请高手指点！！

Answer 1

SQL> select * from a;   ITEM_NO ITEM_SUB_NO ITEM_NAME
---------- ----------- --------------------
         1          11 Aa1
         2          21 A21
         1          12 A12Executed in 0.016 secondsSQL> select * from b;   ITEM_NO ITEM_SUB_NO ITEM_CODE
---------- ----------- --------------------
         1          11 Code11
         2          21 Code21
         2          22 Code22
         1          12 Code12
         3          31 Code31Executed in 0.015 secondsSQL> select * from b where (ITEM_NO,ITEM_SUB_NO) in(select ITEM_NO,max(ITEM_SUB_NO) from A group by ITEM_NO);   ITEM_NO ITEM_SUB_NO ITEM_CODE
---------- ----------- --------------------
         1          12 Code12
         2          21 Code21Executed in 0 secondsSQL>

Answer 2

可以实现, 但效率比较低
一下是我调整的, 没有经过正确性测试,   但我执行了一下, 执行速度可以提高一倍!
 
select  b.*, a.columnName1
from  
    a,
    b , 
   (select  ITEM_NO,max(ITEM_SUB_NO) from  A  group  by  ITEM_NO) c
where a.ITEM_NO = b.ITEM_NO and a.ITEM_SUB_NO = b.ITEM_SUB_NO 
      and b.ITEM_NO = c.ITEM_NO and b.ITEM_SUB_NO = c.ITEM_SUB_NO

Answer 3

SQL> select b.* from b ,(select ITEM_NO,max(ITEM_SUB_NO) ITEM_SUB_NO from A group by ITEM_NO) c where b.item_no = c.ITEM_NO and b.item_sub_no=c.ITEM_SUB_NO;   ITEM_NO ITEM_SUB_NO ITEM_CODE
---------- ----------- --------------------
         1          12 A12
         2          21 A21SQL>

Answer 4

1、应该对a表的item_no item_sub_no建立唯一索引，对b表的item_no和item_sub_no建立普通索引。并且分析a表和表。
2、使用下面的sql语句（在oracle数据库中）
with temp_a as ( select item_no, max(item_sub_no) item_sub_no from a group by item_no) select d.* from temp_a c,b d where c.item_no = d.item_no and c.item_sub_id = d.item_sub_no.
3、整个执行过程只对a进行一次快速索引扫描。

Answer 5

执行计划如下：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=79)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_B' (Cost=2 Card=1 B
          ytes=53)   2    1     NESTED LOOPS (Cost=3 Card=1 Bytes=79)
   3    2       VIEW (Cost=1 Card=1 Bytes=26)
   4    3         SORT (GROUP BY) (Cost=1 Card=1 Bytes=26)
   5    4           INDEX (FULL SCAN) OF 'PK_TEST_A' (UNIQUE)
   6    2       INDEX (RANGE SCAN) OF 'IND_TEST_B' (NON-UNIQUE) (Cost=
          1 Card=1)

Answer 6

select  b.*, a.columnName1
from  a, b 
where a.ITEM_NO = b.ITEM_NO and a.ITEM_SUB_NO = b.ITEM_SUB_NO
and a.ITEM_SUB_NO = (
   select max(ITEM_SUB_NO) from a cc where a.ITEM_NO=cc.ITEM_NO 
)