记录一下有下面函数可以使用。
SELECT
*
FROM
(
SELECT *,
ROW_NUMBER () OVER (PARTITION BY package_name ORDER BY version_code DESC) rn
FROM va_file
) T
WHERE rn = 1
以及一下子就能想到的:
SELECT
vf.*
FROM
va_file vf
JOIN (
SELECT
package_name,
MAX (version_code) AS version_code
FROM
va_file
WHERE
status = 2
GROUP BY
package_name
) tmp ON vf.package_name = tmp.package_name
AND vf.version_code = tmp.version_code
类似的需求有:
1、查找用户每天第一次登录的时间
-- 查找用户每天第一次登录的时间
select * FROM
(
select user_id, create_datetime,
ROW_NUMBER () OVER (PARTITION BY to_char(create_datetime,'yyyy-MM-dd') ORDER BY create_datetime ASC) rn
from user_login_log ORDER BY user_id
) t
where rn = 1
2、继而拓展出类似的需求:根据用户登录记录表查询用户连续登录天数
select
user_id,
min(create_datetime) start_date,
max(create_datetime) end_date,
count(1) running_days
from (
select
user_id,
create_datetime,
date_part('day',create_datetime::timestamp - timestamp'2017-01-01') day_interval,
row_number() over(partition by user_id order by to_char(create_datetime,'yyyy-MM-dd')) day_rank,
(
row_number() over(partition by user_id order by to_char(create_datetime,'yyyy-MM-dd'))
) - (
date_part('day',create_datetime::timestamp - timestamp'2017-01-01')
) diff_value
from (
select * FROM (
select user_id,
create_datetime,
ROW_NUMBER () OVER (PARTITION BY to_char(create_datetime,'yyyy-MM-dd') ORDER BY create_datetime) rn
from user_login_log
) t
where rn = 1
) t
) t
GROUP BY user_id, diff_value
order by user_id, start_date
;