时间戳

首页 > 解决方案 > 我无法从下面的查询中获得 max(reservation_date) 的最后日期。查询也需要很长时间

sql - 我无法从下面的查询中获得 max(reservation_date) 的最后日期。查询也需要很长时间

问题描述

我下面的查询需要很长时间。我不知道我在哪里做错了。希望输出 Resort_name Resource_name last_date("它在预订中预订") 该预订的到达日期

    SELECT
    max(rsv.reservation_date),
    r.name    AS resort_name,
    rsv.name   AS resource_name,
    rsv.reservation_date,
    rv.arrival_date
FROM
    resorti18n           r
    JOIN resourcebasei18n     re ON ( r.resort_id = re.resort_id )
    JOIN reservedresourcebase rsv ON (rsv.resource_id=re.resource_id)
    JOIN reservation          rv ON ( rv.resort_id = re.resort_id )
    JOIN admin_organisation   ao ON ( ao.admin_organisation_id = r.admin_organisation_id )

WHERE
    rsv.type = 'producttype'
    group by 
    r.name,
    rsv.name,
    rsv.reservation_date,
    rv.arrival_date;

标签： sqloracle12c

解决方案

使用 row_number()

 with cte as(SELECT
    rsv.reservation_date,
    row_number() over(partition by  r.name,rsv.name order by rsv.reservation_date desc) rn,
    r.name    AS resort_name,
    rsv.name   AS resource_name,
    rsv.reservation_date,
    rv.arrival_date
FROM
    resorti18n           r
    JOIN resourcebasei18n     re ON ( r.resort_id = re.resort_id )
    JOIN reservedresourcebase rsv ON (rsv.resource_id=re.resource_id)
    JOIN reservation          rv ON ( rv.resort_id = re.resort_id )
    JOIN admin_organisation   ao ON ( ao.admin_organisation_id = r.admin_organisation_id )

WHERE
    rsv.type = 'producttype'
 ) select * from cte where rn=1

使用子查询

select * from (SELECT
        row_number() over(partition by  r.name,rsv.name order by rsv.reservation_date desc) rn,
        r.name    AS resort_name,
        rsv.name   AS resource_name,
        rsv.reservation_date,
        rv.arrival_date
    FROM
        resorti18n           r
        JOIN resourcebasei18n     re ON ( r.resort_id = re.resort_id )
        JOIN reservedresourcebase rsv ON (rsv.resource_id=re.resource_id)
        JOIN reservation          rv ON ( rv.resort_id = re.resort_id )
        JOIN admin_organisation   ao ON ( ao.admin_organisation_id = r.admin_organisation_id )

    WHERE
        rsv.type = 'producttype'
     ) a where a.rn=1

推荐阅读