我需要有关Oracle Sql中相关子查询的帮助. 问题是,第二级深度子查询包含daily.day,因此此查询会导致错误.
I need help with a correlated subquery in Oracle Sql. The problem is, that the second level deep subquery contains the daily.day, so this query results in an error.
DAILY - columns: daily_id, day, emp_details_id, worked_hour EMP_DETAILS - columns: emp_details_id, valid_from, valid_to, detail_type, detail_value我想获取每一行的detail_value,其中行的日期介于ed.valid_from和ed.valid_to之间.然后,我想参加这一天的活动,其中ed.valid_from是最大的(最近的). 所以我想要给定的emp_details_id
I'd like to get the detail_value for each row, where the row's day is between ed.valid_from and ed.valid_to. Then I'd like to take the row for this day, where ed.valid_from is the greatest (most recent). So I'd like the most recent valid detail value for the given emp_details_id
示例:(我只写了所需的列)
Example: (I only wrote the needed columns)
DAILY
day = '2016-03-02', emp_details_id = 1 day = '2016-03-04', emp_details_id = 1EMP_DETAILS
EMP_DETAILS
valid_from = '2016-01-01', valid_to = '2016-12-31', detail_value = 6, emp_details_id = 1 valid_from = '2016-03-02', valid_to = '2016-12-31', detail_value = 7, emp_details_id = 1 valid_from = '2016-03-03', valid_to = '2016-12-31', detail_value = 8, emp_details_id = 1 valid_from = '2016-03-01', valid_to = '2016-12-31', detail_value = 10, emp_details_id = 2结果:
day = '2016-03-02', valid_from = '2016-03-02', valid_to = '2016-12-31', detail_value = 7, emp_details_id = 1 day = '2016-03-04', valid_from = '2016-03-03', valid_to = '2016-12-31', detail_value = 8, emp_details_id = 1我的查询:
SELECT da.*, ed.detail_value FROM DAILY da INNER JOIN EMP_DETAILS ed ON(da.emp_details_id = ed.emp_details_id) WHERE ed.detail_value = (SELECT worktime.detail_value FROM (SELECT ed2.detail_value FROM EMP_DETAILS ed2 WHERE ed2.valid_from <= da.day AND --error ed2.valid_to >= da.day AND --error ed2.emp_details_id = ed.emp_details_id --error ORDER BY ed2.valid_from DESC ) worktime WHERE ROWNUM = 1 )推荐答案
通过使用解析查询对daily记录的最新ed.valid_from日期对联接的行进行排名,可以避免自联接.基本查询类似于:
You can avoid the self-joins by using an analytic query to rank the joined rows by the latest ed.valid_from date for the daily record. The basic query is something like:
SELECT daily.*, ed.*, rank() over (partition by daily.emp_details_id, daily.day order by ed.valid_from DESC) rnk FROM DAILY daily INNER JOIN EMP_DETAILS ed ON daily.emp_details_id = ed.emp_details_id AND ed.valid_from <= daily.day AND ed.valid_to >= daily.day; DAY EMP_DETAILS_ID VALID_FROM VALID_TO DETAIL_VALUE EMP_DETAILS_ID RNK ---------- -------------- ---------- ---------- ------------ -------------- ---------- 2016-03-02 1 2016-03-02 2016-12-31 7 1 1 2016-03-02 1 2016-01-01 2016-12-31 6 1 2 2016-03-04 1 2016-03-03 2016-12-31 8 1 1 2016-03-04 1 2016-03-02 2016-12-31 7 1 2 2016-03-04 1 2016-01-01 2016-12-31 6 1 3具有最大日期的记录排名第1,因此您可以将其放在子查询中并在生成的rnk列上进行过滤:
The record with the greatest date is ranked 1, so you can put that in a subquery and filter on the generated rnk column:
SELECT emp_details_id, day, detail_value FROM ( SELECT daily.day, daily.emp_details_id, ed.detail_value, rank() over (partition by daily.emp_details_id, daily.day order by ed.valid_from DESC) rnk FROM DAILY daily INNER JOIN EMP_DETAILS ed ON daily.emp_details_id = ed.emp_details_id AND ed.valid_from <= daily.day AND ed.valid_to >= daily.day ) WHERE rnk = 1; EMP_DETAILS_ID DAY DETAIL_VALUE -------------- ---------- ------------ 1 2016-03-02 7 1 2016-03-04 8从数据看来您不可能有两个匹配的记录,但是如果您这样做了(如果7和8我们都从同一日期开始生效),那么它将返回两行.您将需要调整partition by子句以选择打破平局的方式. (您也可以使用density_rank,row_number等,但是同样适用-如果有平局,则应指定如何打破).
From the data is doesn't look likely that you'd have two matching records, but if you did (if 7 and 8 we both valid from the same date) then this would return two rows. You would need to adjust the partition by clause to choose how to break the tie. (You can also use dense_rank, row_number etc. but the same applies - if there can be a tie you should specify how to break it).
更多推荐
Oracle SQL中的相关查询
发布评论