园区非园区不一致零件清单

最新推荐文章于 2025-10-29 14:16:27 发布

原创最新推荐文章于 2025-10-29 14:16:27 发布 · 298 阅读

0 ·

CC 4.0 BY-SA版权

工作所需专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于从特定状态和类型的记录中获取不同货币的价格信息，并通过多个子查询进行数据筛选与汇总。

本体：

select t0.*, t1.RMBprice, t2.USDprice
from (select cgicode, orderclass
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '02')
and ((rtexcetodate is null and
rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < rtexcetodate))
group by cgicode, orderclass
having max(splrtupric) <> min(splrtupric)) t0

left join (select cgicode, orderclass, splrtupric as RMBprice
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '02')
and ((rtexcetodate is null and
rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < rtexcetodate))
and fcode = 'RMB'

) t1
on t0.cgicode = t1.cgicode
and t0.orderclass = t1.orderclass

left join (select cgicode, orderclass, splrtupric as USDprice
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '02')
and ((rtexcetodate is null and
rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(rtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < rtexcetodate))
and fcode = 'USD'

) t2
on t0.cgicode = t2.cgicode
and t0.orderclass = t2.orderclass

VT：

select t0.*, t1.RMBprice, t2.USDprice
from (select cgicode, orderclass
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '03')
and ((vtexcetodate is null and
vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < vtexcetodate))
group by cgicode, orderclass
having max(ncicvtupric) <> min(ncicvtupric)) t0

left join (select cgicode, orderclass, ncicvtupric as RMBprice
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '03')
and ((vtexcetodate is null and
vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < vtexcetodate))
and fcode = 'RMB'

) t1
on t0.cgicode = t1.cgicode
and t0.orderclass = t1.orderclass

left join (select cgicode, orderclass, ncicvtupric as USDprice
from askquotelist
where status >= '70'
and (asktype = '01' or asktype = '03')
and ((vtexcetodate is null and
vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy')) or
(vtexcedate <= TO_DATE('01/01/2019', 'mm/dd/yyyy') and
TO_DATE('01/01/2019', 'mm/dd/yyyy') < vtexcetodate))
and fcode = 'USD'

) t2
on t0.cgicode = t2.cgicode
and t0.orderclass = t2.orderclass