对于表tClass中匹配给定where子句的每一行，
加入tEv的第一行，按时间排序，其中tEv.class_id = tClass.class_id
以下代码抛出错误ORA-01799：列可能不是外部连接到子查询
select c.class_id, c.class_name, e.start_time, e.ev_id from tClass c left join tEv e on ( e.ev_id = ( select ss1.ev_id from ( select ed.ev_id from tEvDisp ed, tEv e where ed.class_id = c.class_id and ed.viewable = 'Y' and ed.display_until > localtimestamp and e.ev_id = ed.ev_id order by e.start_time ) ss1 where rownum = 1 ) ) where c.is_matching = 'Y';如何改写以完成所描述的内容？
以上是针对oracle，但需要在sqlite中工作（必要时替换）
For each row from table tClass matching a given where clause,
join on the first row in tEv, sorted by time, where tEv.class_id = tClass.class_id
The following code throws the error ORA-01799: a column may not be outer-joined to a subquery
select c.class_id, c.class_name, e.start_time, e.ev_id from tClass c left join tEv e on ( e.ev_id = ( select ss1.ev_id from ( select ed.ev_id from tEvDisp ed, tEv e where ed.class_id = c.class_id and ed.viewable = 'Y' and ed.display_until > localtimestamp and e.ev_id = ed.ev_id order by e.start_time ) ss1 where rownum = 1 ) ) where c.is_matching = 'Y';How can this be rewritten to do what is described?
The above is for oracle, but needs to work in sqlite (substituting where necessary)
最满意答案
不知道SQLite - 如果这不起作用则需要单独的问题 - 但对于Oracle，你可以这样做：
select c.class_id, c.class_name, e.start_time, e.ev_id from tClass c left join ( select class_id, ev_id, start_time from ( select ed.class_id, ed.ev_id, e.start_time, row_number() over (partition by ed.class_id order by e.start_time) as rn from tEvDisp ed join tEv e on e.ev_id = ed.ev_id where ed.viewable = 'Y' and ed.display_until > localtimestamp ) where rn = 1 ) e on e.class_id = c.class_id where c.is_matching = 'Y';这使用一个查询最多tEv数据的子查询，使用解析row_number()来识别每个class_id的最新数据，该数据受rn = 1过滤器的限制。
该子查询由每个class_id最多一行class_id ，然后使用左外连接对tClass 。
No idea about SQLite - that would need to be a separate question if this doesn't work - but for Oracle you could do something like this:
select c.class_id, c.class_name, e.start_time, e.ev_id from tClass c left join ( select class_id, ev_id, start_time from ( select ed.class_id, ed.ev_id, e.start_time, row_number() over (partition by ed.class_id order by e.start_time) as rn from tEvDisp ed join tEv e on e.ev_id = ed.ev_id where ed.viewable = 'Y' and ed.display_until > localtimestamp ) where rn = 1 ) e on e.class_id = c.class_id where c.is_matching = 'Y';This uses a subquery which finds the most tEv data, using an analytic row_number() to identify the latest data for each class_id, which is restricted by the rn = 1 filter.
That subquery, consisting of at most one row per class_id, is then used the left outer join against tClass.
更多推荐
发布评论