//1根据A分组,查B的最大值
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
//2根据A分组,查B为最大值的所有C列
select tab.*,bf.RELATED_CAVITY
from (
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
) tab
left join bom_function bf on bf.DER_DESIGN_NAME = tab.DER_DESIGN_NAME and bf.DER_DESIGN_VERSION = tab.DER_DESIGN_VERSION
//3根据A分组,查B的最大值的所有C列,每组保存一条并将C列数据拼接成字符串
SELECT DER_DESIGN_NAME,DER_DESIGN_VERSION,ListAgg(to_char(RELATED_CAVITY),',') WITHIN GROUP(ORDER BY DER_DESIGN_NAME) AS RELATED_CAVITY
FROM (
select tab.*,bf.RELATED_CAVITY
from (
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
) tab
left join bom_function bf on bf.DER_DESIGN_NAME = tab.DER_DESIGN_NAME and bf.DER_DESIGN_VERSION = tab.DER_DESIGN_VERSION
) tab GROUP BY DER_DESIGN_NAME,DER_DESIGN_VERSION
//4连表查询
select bf.PROJECT_NAME,bf.COMPOSITE_NAME,bf.DER_DESIGN_NAME,bf.DER_HARNESSPN,bf.DER_DESIGN_VERSION,bf.PARTPN,tab.RELATED_CAVITY
from bom_function bf
left join (
SELECT DER_DESIGN_NAME,DER_DESIGN_VERSION,ListAgg(to_char(RELATED_CAVITY),',') WITHIN GROUP(ORDER BY DER_DESIGN_NAME) AS RELATED_CAVITY
FROM (
select tab.*,bf.RELATED_CAVITY
from (
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
) tab
left join bom_function bf on bf.DER_DESIGN_NAME = tab.DER_DESIGN_NAME and bf.DER_DESIGN_VERSION = tab.DER_DESIGN_VERSION
) tab GROUP BY DER_DESIGN_NAME,DER_DESIGN_VERSION
) tab on tab.DER_DESIGN_NAME = bf.DER_DESIGN_NAME and tab.DER_DESIGN_VERSION = bf.DER_DESIGN_VERSION
where bf.type_code = 'Pl01'
//5分组后只保留一条数据
select * from (SELECT tab.*,row_number() OVER(PARTITION BY DER_DESIGN_NAME order by DER_DESIGN_VERSION desc) rn
FROM (
select bf.PROJECT_NAME,bf.COMPOSITE_NAME,bf.DER_DESIGN_NAME,bf.DER_HARNESSPN,bf.DER_DESIGN_VERSION,bf.PARTPN,tab.RELATED_CAVITY
from bom_function bf
left join (
SELECT DER_DESIGN_NAME,DER_DESIGN_VERSION,ListAgg(to_char(RELATED_CAVITY),',') WITHIN GROUP(ORDER BY DER_DESIGN_NAME) AS RELATED_CAVITY
FROM (
select tab.*,bf.RELATED_CAVITY
from (
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
) tab
left join bom_function bf on bf.DER_DESIGN_NAME = tab.DER_DESIGN_NAME and bf.DER_DESIGN_VERSION = tab.DER_DESIGN_VERSION
) tab GROUP BY DER_DESIGN_NAME,DER_DESIGN_VERSION
) tab on tab.DER_DESIGN_NAME = bf.DER_DESIGN_NAME and tab.DER_DESIGN_VERSION = bf.DER_DESIGN_VERSION
where bf.type_code = 'Pl01'
) tab
) t where rn=1
//6连表查询得到最终结果
select tab.*,br.this_cost
from (
select * from (SELECT tab.*,row_number() OVER(PARTITION BY DER_DESIGN_NAME order by DER_DESIGN_VERSION desc) rn
FROM (
select bf.PROJECT_NAME,bf.COMPOSITE_NAME,bf.DER_DESIGN_NAME,bf.DER_HARNESSPN,bf.DER_DESIGN_VERSION,bf.PARTPN,tab.RELATED_CAVITY
from bom_function bf
left join (
SELECT DER_DESIGN_NAME,DER_DESIGN_VERSION,ListAgg(to_char(RELATED_CAVITY),',') WITHIN GROUP(ORDER BY DER_DESIGN_NAME) AS RELATED_CAVITY
FROM (
select tab.*,bf.RELATED_CAVITY
from (
select DER_DESIGN_NAME ,max(DER_DESIGN_VERSION) DER_DESIGN_VERSION from bom_function group by DER_DESIGN_NAME
) tab
left join bom_function bf on bf.DER_DESIGN_NAME = tab.DER_DESIGN_NAME and bf.DER_DESIGN_VERSION = tab.DER_DESIGN_VERSION
) tab GROUP BY DER_DESIGN_NAME,DER_DESIGN_VERSION
) tab on tab.DER_DESIGN_NAME = bf.DER_DESIGN_NAME and tab.DER_DESIGN_VERSION = bf.DER_DESIGN_VERSION
where bf.type_code = 'Pl01'
) tab
) t where rn=1
) tab
left join bom_refreshattr br on tab.partpn = br.partpn
