Oracle 在多条记录上选择最大日期

Oracle Select Max Date on Multiple records

提问by danielw-msu

I've got the following SELECT statement, and based on what I've seen here: SQL Select Max Date with Multiple recordsI'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 语句，并基于我在这里看到的内容：SQL Select Max Date with Multiple records我的示例设置方式相同。我在 Oracle 11g 上。它不是为每个 asset_tag 返回一条记录，而是返回倍数。没有源表中那么多的记录，但比（我认为）应该多。如果我运行内部 SELECT 语句，它还会返回正确的记录集（每个资产标签 1 个），这真的让我感到困惑。

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

Any suggestions?

有什么建议？

采纳答案by Brian Camire

I think you need to add...

我想你需要补充...

AND outside.asset_tag=inside.asset_tag

...to the criteria in your ONlist.

...符合您ON列表中的条件。

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

另外一个RIGHT OUTER JOIN是没有必要的。一个INNER JOIN可以得到同样的结果（和可能更efficicient），因为会出现不能被组合asset_tag，并last_read_date在不存在的子查询mp_vehicle_asset_profile。

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. 相比之下，@Lamak 的基于分析的答案将在这种情况下任意选择一行。

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

您的意见建议，要通过与最高采摘行打破平局mtr_reading的last_read_date。

You could modify @Lamak's analyic-based answer to do this by changing the ORDER BYin the OVERclause to:

您可以通过ORDER BY将OVER子句中的更改为：

ORDER BY last_read_date DESC, mtr_reading DESC

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.

如果仍然存在联系（即多行具有相同的asset_tag, last_read_date, 和mtr_reading），查询将再次随意选择一行。

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

您可以修改我的基于聚合的答案以使用最高值打破平局mtr_reading，如下所示：

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

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.

如果还有关系（即多行与同asset_tag，last_read_date和mtr_reading），查询可能再次超过一个行返回。

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_readingare 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 TRUEwhen a null is involved.

基于分析和基于聚合的答案不同的另一种方式是它们对空值的处理。如果有任何的asset_tag，last_read_date或mtr_reading为空，基于分析，答案将返回相关行，但基于总-one不会（因为在平等条件下参与不计算TRUE当空参与。

回答by Lamak

Try with analytical functions:

尝试使用分析函数：

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 (  SELECT *, ROW_NUMBER() OVER(PARTITION BY asset_tag ORDER BY last_read_date DESC) Corr
        FROM mp_vehicle_asset_profile) outside
WHERE Corr = 1