Postgresql的Select with,数组等高级应用例子

用 with子句可在主select语句前先定义子集,array_agg则可以将记录集转为数组,unnest则可以将数组转为记录集,如下SQL,一个SQL即可显示指定产品流水号的对应生产工单的扫描模板工位明细,当前产品做到哪个工位,此产品经过这些工位的状态(是否经过,QC结果等)

with t96 as ( --先找出产品管理号在t96表中最后生产状态,子集结果为"101D2222222222";"JB048985";"OBA_VI"
select f96_mgtbarcd,f96_order,f96_station from t96_pd_log 
where f96_mgtbarcd='101D2222222222' 
order by f96_create_dt desc limit 1
),t1 as ( --
select f9m_seq,f9m_group,f9m_op_type,case when f9m_op_type='PD-AS' or f9m_op_type='PD-PA' then  --如果为装配工位,则从t99表中将此组的所有工位找出并放到stations数组中
	(select array_agg(f99_station) from (
	select f99_station from (select f99_station from t99_op_def inner join t9e_job_op_template on f9e_jobno=f9m_jobno
	and f9e_template=f99_template and f99_group=f9m_group and f99_line_type=f9m_line_type) as f group by f99_station) 
	as f )
else --如果是QC/QA工位则从t94表中将此组的所有QC工位找出并放于stations数组中
	(select array_agg(f94_opseq) from t94_qc_master where f94_plant='DG' and f94_model=f9m_jobno and f94_group=f9m_group and f94_line_type=f9m_line_type)
end as stations
from t9m_job_group as a,t96 
where f9m_jobno= f96_order
)
select t1.*,stations@>array[f96_station] as lastpoint  --是否为当前所处工位,coalesce((select case when f96_op='PD-QC' then 'NG' 
	      when f96_op='QC-OK' then 'OK'
	      else 'Y' end from unnest(stations) as station inner join t96_pd_log as b on b.f96_mgtbarcd=t96.f96_mgtbarcd 
	      and b.f96_station=station order by f96_create_dt desc limit 1),'N')
	as assebed -- N-没记录,Y-PD记录,OK-QC Pass,NG-QC NG
from  t1,t96
order by f9m_seq

结果: