PostgreSQL 知识点总结

推荐非常方便好用的一个网站：PostgreSQL 参考资料 (sjkjc.com)

PostgreSQL concat_ws() 函数使用指南

 

PostgreSQL string_agg() 函数使用指南

PostgreSQL dense_rank() 函数使用指南

PostgreSQL row_number() 函数使用指南

一个实例：

select pmt.plt_oid as oid,
pmt.plt_project as project,
pmt.plt_customer as customer,
pmt.plt_model as model,
pmt.plt_initiator as initiator,
pmt.plt_status as status,
pmt.plt_builddate as "buildDate",
pmt.plt_transferoutsite as "transferOutSite",
pmt.plt_transferinsite as "transferInSite",
pmt.plt_checklistoid as checklistoid,
pmt.plt_buildplanfiles as buildplanfiles,
pmt.plt_outcharger as "outCharger",
pmt.plt_incharger as "inCharger",
pbt.plt_taskid as taskid,
pbt.plt_taskname as taskname,
cast((case when progress.currenttask is not null then progress.currenttask else pbt.plt_taskname end) as varchar) as "currentTask",
cast(pbt.plt_taskinstanceinfo as varchar) as "taskInstanceInfo",
pbt.plt_state as state,
pbt.plt_lastmodifytime as "lastModifyTime",
countItems.outcount as "outCount",
countItems.outapprovecount as "outApproveCount",
countItems.incount as "inCount",
countItems.inapprovecount as "inApproveCount"
from plt_mpm_transferprocess pmt 
left join (
	select transferprocessoid as transferprocessoid, 
	string_agg(distinct concat(outsite, ':', outcount), ',  ') as outcount, 
	string_agg(distinct concat(outsite, ':', outapprovecount), ',  ') as outapprovecount, 
	string_agg(concat(insite, ':', incount), ',  ') as incount,
	string_agg(concat(insite, ':', inapprovecount), ',  ') as inapprovecount
	from (
		select pmi.plt_transferprocessoid as transferprocessoid, 
		split_part(pmp.plt_pic, '#', 1) as outsite, 
		pmi.plt_insite as insite,
		concat(count(pmp.plt_outstatus), '/', count(*)) as outcount,
		concat(count(pmp.plt_outapprovestatus), '/', count(*)) as outapprovecount,
		concat(count(pmi.plt_instatus), '/', count(*)) as incount,
		concat(count(pmi.plt_inapprovestatus), '/', count(*)) as inapprovecount
		from plt_mpm_processcheckitem pmp
		left join plt_mpm_inprocesscheckitem pmi on pmi.plt_processcheckitemoid = pmp.plt_oid
		group by pmi.plt_transferprocessoid, pmi.plt_insite, split_part(pmp.plt_pic, '#', 1)
	) as counts
	group by transferprocessoid
) as countItems on countItems.transferprocessoid = pmt.plt_oid
left join (
	select *, row_number() over (partition by plt_targetoid order by plt_lastmodifytime desc) as r
	from plt_bpm_taskinstance
) as pbt on pbt.plt_targetoid = pmt.plt_oid and r = 1
left join (
	select plt_targetoid as targetoid, string_agg(concat(site, ':', plt_taskname), ',  ') as currenttask
	from (
		select *
		from (
			select plt_targetoid, substring(plt_taskid, 1, 3) as site, plt_taskid, plt_taskname, row_number() over(partition by substring(plt_taskid, 1, 3) order by plt_createtime desc) as ranks
			from plt_bpm_taskinstance pbt 
			where plt_targetclass = 'transferProcess'
		) alltask
		where ranks = 1 
	) task
	group by plt_targetoid
) as progress on progress.targetoid = pmt.plt_oid
order by pmt.plt_lastmodifytime desc