Oracle SQL中的相关查询 [英] Correlated query in oracle sql
问题描述
我需要有关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 = 1
EMP_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中的相关查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
