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

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

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

with t96 as ( --先找出产品管理号在t96表中最后生产状态,子集结果为"101D1111111111";"JB048985";"OBA_VI"select f96_mgtbarcd,f96_order,f96_station from t96_pd_log where f96_mgtbarcd='101D1111111111' 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 stationsfrom 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 NGfrom  t1,t96order by f9m_seq

结果:

总结

以上是内存溢出为你收集整理的Postgresql的Select with,数组等高级应用例子全部内容，希望文章能够帮你解决Postgresql的Select with,数组等高级应用例子所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。