如何在内部连接中使用rank()？

Question

我想加入我的奶酪表与新鲜度，以获得奶酪和奶酪的新鲜度代码，其中每个奶酪id的最大(Seq_no)是模具。

当使用rank()时，我在哪里加入到FRESHNESS？

CHEESE                                    FRESHNESS
CHEESE_ID  SEQ_NO  FRESH_CODE             FRESH_CODE   FRESH_DESC      
=================================         ========================
  1         1        MOLD                 MOLD         MOLDY CHEESE    
  1        23        FRSH                 FRSH         EDIBLE
  1        34        FRSH
  2         2        FRSH
  2        18        MOLD
  3         3        MOLD
  3         5        MOLD
  3         7        MOLD


 DESIRED RESULT
 ==========================
 CHEESE_ID  SEQ_NO  FRESH_CODE  FRESH_DESC      SEQ_RANK
 2           18     MOLD        MOLDY CHEESE    1
 3            7     MOLD        MOLDY CHEESE    1

下面是我用来获取所需序列号的代码。

select 
       cheese_id,seq_no,fresh_code,seq_rank
  from ( select 
         cheese_id,seq_no, fresh_code, 
         rank() over (partition by cheese_id
                          order by seq_no desc) seq_rank
from cheese
where seq_rank = 1
 and  fresh_code = 'MOLD'

Answer 1

您可以在子查询中进行连接

select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
  from ( select cheese_id,
                seq_no, 
                fresh_code, 
                fresh_desc,
                rank() over (partition by cheese_id
                                 order by seq_no desc) seq_rank
          from cheese
               join freshness using (fresh_code) )
 where seq_rank = 1
   and fresh_code = 'MOLD'

或者，您可以连接到子查询

select cheese_id,seq_no,fresh_code,fresh_desc,seq_rank
  from ( select cheese_id,
                seq_no, 
                fresh_code, 
                fresh_desc,
                rank() over (partition by cheese_id
                                 order by seq_no desc) seq_rank
          from cheese ) cheese_outer
       join freshness using (fresh_code)
 where seq_rank = 1
   and fresh_code = 'MOLD'