我无法从下面获取最大(Reservation_date)的最后日期query.Also查询需要很长时间

Question

我下面的查询花费了很长时间。我不知道我在哪里输出预订的到达日期(“it‘s wrong.Desire Resort_name in 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;

Answer 1

使用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

Usign子查询

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