select * from ( select *, rank() over(partition by pac order by common desc,pbc asc) as crank from ( select distinct t1.pro_c_id as pac, t2.pro_c_id as pbc, count(t2.pro_c_id) as common from property as t1 join ( select distinct pro_c_id, pro_pif_id from property where pro_type = 1 )as t2 on t2.pro_pif_id = t1.pro_pif_id where pro_type = 1 and t2.pro_c_id <> t1.pro_c_id group by t1.pro_c_id, t2.pro_c_id ) as t3 ) as t4 where crank <3

这是一个 MySQL 查询语句，用于在 property 表中找出持有相同基金的用户对，并按照相同基金数量和第二个用户 ID 排序，最终返回前两个用户对。查询结果包括两个用户 ID，以及共同持有的基金数量。

查询中使用了窗口函数 RANK、DISTINCT、JOIN、GROUP BY 等语句。首先，通过子查询 t2 查询出所有持有基金的用户，并将其与子查询 t1 中的用户进行连接，计算它们之间相同基金的数量，结果保存为 t3 表。然后，在外部查询中使用窗口函数 RANK，对每个持有基金的用户进行分组，按照相同基金数量和第二个用户 ID 进行排序（common desc, pbc asc），并为每个用户对生成一个排名（crank）。最后，通过 WHERE 子句筛选出排名前两个的用户对。

需要注意的是，在查询中使用了 DISTINCT 关键字，用于去除重复的基金记录。例如，如果一对用户都持有同一基金，但在 property 表中存在多条相同的记录，DISTINCT 将会去除这些重复记录。

相关问题

select distinct exam_id, duration, release_time from (select exam_id as exam_id, duration, release_time, sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub from ( select e_i.exam_id, duration, release_time, timestampdiff(minute, start_time, submit_time) as costtime, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1, row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2 from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where submit_time is not null ) table1 group by exam_id ) table2 where sub * 2 >= duration order by exam_id desc;把这段sql改写成oracle语法

SELECT DISTINCT exam_id, duration, release_time
FROM (
SELECT exam_id, duration, release_time, SUM(CASE WHEN rank1 = 2 THEN costtime WHEN rank2 = 2 THEN -costtime ELSE 0 END) AS sub
FROM (
SELECT e_i.exam_id, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS costtime,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC) rank1,
ROW_NUMBER() OVER(PARTITION BY e_r.exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) ASC) rank2
FROM exam_record e_r JOIN examination_info e_i
ON e_r.exam_id = e_i.exam_id
WHERE submit_time IS NOT NULL
) table1
GROUP BY exam_id, duration, release_time
) table2
WHERE sub * 2 >= duration
ORDER BY exam_id DESC;