select a.*,b.seq_val from a a left join b b on a.id = b.id and a.date = b.date and a.seq >= b.seq_start and a.seq <= b.seq_end;
select a.*,(select seq_val from b where b.id=a.id and b.date=a.date and a.seq>b.seq_start and a.seq<b.seq_end) from a 你这个是语法错误
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd)); 这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么
这道题的瓶颈就在于最终on条件 on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd))oracle的逻辑判断机制与c语言并没有什么不同,and条件从前匹配到后,有不符合立马false不继续判断,但or则必须全部判断完才能有结果,因此两者的开销是天壤之别 把条件都改成用and判断,然后union试试看吧
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd)); 这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd)); 这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了
SELECT T.*, CASE WHEN T.SEQ >= T2.SEQSTART AND T.SEQ <= T2.SEQEND THEN T2.SEQVAL ELSE NULL END AS SEQVAL FROM A T, B T2 WHERE T.ID = T2.ID AND T2.DATES = T.DATES
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd)); 这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了最后我把left join 都用where实现的,然后两个结果再left join比对一下,执行效率比之前快了近几百倍
select a.*, case a.seq when between b.seq_start and b.seq_end then b.seq_val else '' end from a a inner join b b on a.id = b.id and a.date = b.date;
我就这么写的,问题是,你考虑过这语句效率问题么,我贴出来实际的代码你感受下select e.*,f.completion_id,f.completion_name,f.completion_number,f.top_md,f.bottom_md from ( select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk from daa05@dzcxyh c,( select jh,create_date, count(completion_id) cds from ( select a.well_desc jh,b.completion_id, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id ) group by jh,create_date ) d where c.jh=d.jh ) e left join ( select a.well_desc jh, to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date, b.completion_id,b.completion_name,b.completion_number, nvl(b.top_md,0) top_md, nvl(b.bottom_md,0) bottom_md from cd_well_source@a2 a,cd_completion_t@a2 b where b.well_id=a.well_id order by a.well_desc ) f on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd)); 这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了以后记得把问题描述清楚点嘛,无关SQL写法问题,是优化问题。 你可以把你自已的SQL贴上,环境背景也描述下,得到的回答才是有参考价值的。
from a a
left join b b on a.id = b.id
and a.date = b.date
and a.seq >= b.seq_start
and a.seq <= b.seq_end;
select a.*,(select seq_val from b where b.id=a.id and b.date=a.date and a.seq>b.seq_start and a.seq<b.seq_end) from a
你这个是语法错误
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd))oracle的逻辑判断机制与c语言并没有什么不同,and条件从前匹配到后,有不符合立马false不继续判断,但or则必须全部判断完才能有结果,因此两者的开销是天壤之别
把条件都改成用and判断,然后union试试看吧
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了
CASE
WHEN T.SEQ >= T2.SEQSTART AND T.SEQ <= T2.SEQEND THEN
T2.SEQVAL
ELSE
NULL
END AS SEQVAL
FROM A T, B T2
WHERE T.ID = T2.ID
AND T2.DATES = T.DATES
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了最后我把left join 都用where实现的,然后两个结果再left join比对一下,执行效率比之前快了近几百倍
case a.seq
when between b.seq_start and b.seq_end then b.seq_val
else ''
end
from a a
inner join b b on a.id = b.id
and a.date = b.date;
from (
select c.jh,d.create_date,c.yczmc,c.xch,d.cds,c.syds,c.syhd,c.yxhd,c.yxstl,c.dcjsjg,c.skqk
from daa05@dzcxyh c,(
select jh,create_date,
count(completion_id) cds
from (
select a.well_desc jh,b.completion_id,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
)
group by jh,create_date
) d
where c.jh=d.jh
) e left join (
select a.well_desc jh,
to_date(to_char(b.create_date,'yyyy-MM-dd'),'yyyy-MM-dd') create_date,
b.completion_id,b.completion_name,b.completion_number,
nvl(b.top_md,0) top_md,
nvl(b.bottom_md,0) bottom_md
from cd_well_source@a2 a,cd_completion_t@a2 b
where b.well_id=a.well_id
order by a.well_desc
) f
on e.jh=f.jh and e.create_date=f.create_date and (f.top_md=0 or f.bottom_md=0 or (f.top_md<=e.syds and f.bottom_md>=e.syds+e.syhd));
这个问题根本就不是left jion on的问题, 也不是他们回答的有问题,而是你自己问的有问题,通过dblink的视图关联的结果集和你描述的一张表是一个概念么你说的也对,按理说a_liujian回答的一点没错,最后还是如Xinke57说的,我检查了下,确实and和or逻辑的开销不是一个层级的,结贴给分了你之前提的问题,我就说用union试试了,只不过就是看着麻烦点,其实如果真是两个表关联估计也差不了多少,只不过dblink的视图关联的结果集估计就慢的要死了以后记得把问题描述清楚点嘛,无关SQL写法问题,是优化问题。
你可以把你自已的SQL贴上,环境背景也描述下,得到的回答才是有参考价值的。