我认为您需要添加...

I think you need to add...

AND outside.asset_tag=inside.asset_tag

...按照ON列表中的条件.

...to the criteria in your ON list.

也不需要RIGHT OUTER JOIN. INNER JOIN将给出相同的结果(并且可能更有效)，因为在子查询中不能存在asset_tag和last_read_date的组合，而这些组合在mp_vehicle_asset_profile中不存在.

Also a RIGHT OUTER JOIN is not needed. An INNER JOIN will give the same results (and may be more efficicient), since there will be cannot be be combinations of asset_tag and last_read_date in the subquery that do not exist in mp_vehicle_asset_profile.

即使如此，如果存在纽带"，则查询可能会为每个资产标签返回多于一行的内容，即具有相同last_read_date的多行.相比之下，@ Lamak基于分析的答案将在这种情况下任意选择一行.

Even then, the query may return more than one row per asset tag if there are "ties" -- that is, multiple rows with the same last_read_date. In contrast, @Lamak's analytic-based answer will arbitrarily pick exactly one row this situation.

您的评论建议您通过为last_read_date选择最高mtr_reading的行来打破平局.

Your comment suggests that you want to break ties by picking the row with highest mtr_reading for the last_read_date.

您可以通过将OVER子句中的ORDER BY更改为以下内容来修改@Lamak基于分析的答案:

You could modify @Lamak's analyic-based answer to do this by changing the ORDER BY in the OVER clause to:

ORDER BY last_read_date DESC, mtr_reading DESC

如果仍然存在联系(即具有相同asset_tag，last_read_date和mtr_reading的多行)，查询将再次自动选择精确的一行.

If there are still ties (that is, multiple rows with the same asset_tag, last_read_date, and mtr_reading), the query will again abritrarily pick exactly one row.

您可以使用最高的mtr_reading来修改我基于汇总的答案，以打破平局:

You could modify my aggregate-based answer to break ties using highest mtr_reading as follows:

SELECT  
    outside.asset_tag, 
    outside.description,
    outside.asset_type,
    outside.asset_group,
    outside.status_code,
    outside.license_no,
    outside.rentable_yn,
    outside.manufacture_code,
    outside.model,
    outside.manufacture_vin,
    outside.vehicle_yr,
    outside.meter_id,
    outside.mtr_uom,
    outside.mtr_reading,
    outside.last_read_date 
FROM 
    mp_vehicle_asset_profile outside 
    INNER JOIN 
    ( 
        SELECT
            asset_tag, 
            MAX(last_read_date) AS last_read_date,
            MAX(mtr_reading) KEEP (DENSE_RANK FIRST ORDER BY last_read_date DESC) AS mtr_reading
        FROM
            mp_vehicle_asset_profile 
        GROUP BY
            asset_tag 
    ) inside 
    ON 
        outside.asset_tag = inside.asset_tag
        AND
        outside.last_read_date = inside.last_read_date
        AND
        outside.mtr_reading = inside.mtr_reading

如果仍然存在联系(即具有相同的asset_tag，last_read_date和mtr_reading的多行)，查询可能会再次返回多个行.

If there are still ties (that is, multiple rows with the same asset_tag, last_read_date, and mtr_reading), the query may again return more than one row.

基于分析的答案和基于汇总的答案不同的另一种方式是它们对null的处理.如果asset_tag，last_read_date或mtr_reading中的任何一个为空，则基于分析的答案将返回相关的行，但基于汇总的答案将不返回(因为联接中的相等条件不会求值为当涉及null时.

One other way that the analytic- and aggregate-based answers differ is in their treatment of nulls. If any of asset_tag, last_read_date, or mtr_reading are null, the analytic-based answer will return related rows, but the aggregate-based one will not (because the equality conditions in the join do not evaluate to TRUE when a null is involved.