Oracle在多个记录上选择最大日期 [英] Oracle Select Max Date on Multiple records

问题描述

基于下面的内容，我得到了以下SELECT语句:

I've got the following SELECT statement, and based on what I've seen here: SQL Select Max Date with Multiple records I've got my example set up the same way. I'm on Oracle 11g. Instead of returning one record for each asset_tag, it's returning multiples. Not as many records as in the source table, but more than (I think) it should be. If I run the inner SELECT statement, it also returns the correct set of records (1 per asset_tag), which really has me stumped.

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
RIGHT OUTER JOIN
  (
  SELECT asset_tag, max(last_read_date) as last_read_date
  FROM mp_vehicle_asset_profile
  group by asset_tag
  ) inside
ON outside.last_read_date=inside.last_read_date

有什么建议吗?

推荐答案

我认为您需要添加...

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.

这篇关于Oracle在多个记录上选择最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！