Mega查询中的子查询

Question

我已经搜索过了，但找不到符合我需要的示例。也许我迷失在我的查询的许多连接中……

我从三个Oracle11g表返回数据- ATE_TESTS、ATE_DATA和TM_CONDITION_DYNAMIC。该查询还有其他表要联接这些表。事实上，有不少于7个Join。

在连接的多个方面，ATE_DATA可能有多个记录，但我只需要最后写入的行。ATE_DATA有一个递增的主键，我想在子查询的聚合函数MAX(DATA_PK)中使用它。我认为它应该在主查询的WHERE子句中，但我不知道如何实现它，而且可能有更好的方法。

也许我会受过教育？

我的问题是：

SELECT ate_serial, data_data, dyn_value
FROM   ate_tests
       LEFT JOIN ate_test_procedure
         ON ate_tests.ate_pk = ate_test_procedure.proc_ate_test_fk
       LEFT JOIN ate_test_data
         ON ate_test_procedure.proc_pk = ate_test_data.data_ate_test_procedure_fk
       LEFT JOIN tm_test_procedure
         ON ate_test_procedure.proc_test_procedure = tm_test_procedure.proc_pk
       LEFT JOIN tm_test_specification
         ON ate_test_data.data_specification = tm_test_specification.spec_pk
       LEFT JOIN tm_test_condition_dynamic
         ON tm_test_specification.spec_condition_set_fk = tm_test_condition_dynamic.dyn_condition_set_fk
       LEFT JOIN tm_test_sequences
         ON ate_tests.ate_sequence_fk = tm_test_sequences.seq_pk
       LEFT JOIN lu_tm_products_model
         ON tm_test_sequences.seq_model = lu_tm_products_model.lumod_pk
WHERE  upper(spec_name) = 'POWER'
AND    lumod_model = 'AMP'
AND    dyn_value = '136'
AND    ate_yield = 1
AND    upper(proc_procedure_name) = 'FINAL TEST'
AND    proc_report = 1
AND    proc_status = 1
ORDER BY ate_serial, dyn_value

Answer 1

...但我只想要最后写的那一行。

这是典型的top-n查询。假设表a包含关于蔬菜的信息、表b它们在不同商店的历史价格以及关于这些商店的s信息。您只对最后的价格感兴趣，您可以使用函数row_number() (或max() ... keep dense rank last...)找到它。

with a as (select 1 vid, 'Tomato' name from dual union all
           select 2 vid, 'Potato' name from dual union all
           select 3 vid, 'Garlic' name from dual),

     b as (select 1 pid, 1 vid, 1 sid, 11.5 price from dual union all 
           select 2 pid, 3 vid, 1 sid, 31.8 price from dual union all
           select 3 pid, 1 vid, 1 sid, 13.2 price from dual union all
           select 4 pid, 1 vid, 2 sid, 12.7 price from dual ),

     s as (select 1 sid, 'Best Vegetables'  name from dual union all
           select 2 sid, 'Organic Products' name from dual)

select a.vid, a.name, s.name as shop, p.price as last_price
  from a 
    left join (select vid, sid, price 
                 from (select vid, sid, price, 
                              row_number() over (partition by vid order by pid desc) rn 
                         from b)
                 where rn = 1) p
      on p.vid = a.vid
    left join s on s.sid = p.sid
  order by a.vid

输出：

VID  Name      Shop                Price
---  --------  ------------------  -------
  1  Tomato    Organic Products       12.7
  2  Potato
  3  Garlic    Best Vegetables        31.8