【Pgsql】笔记

pgsql小笔记

SELECT
	d."JYCode" 交易编号,    -- 字符串
	to_char( d."DepositDate", 'YYYY-MM-DD' ) 转定日期,   --日期格式化
	concat_ws ( '-', d."SigningBrokerName", d."SigningBrokerPhone" ) AS "经纪人信息",     -- 字符串拼接
	( SELECT string_agg ( "MeansTypeNm", ';' ) FROM "BackupFiles" WHERE "DeliverCaseId" = d."Id" ) 备件明细,   -- 列转行
(
	SELECT
		string_agg (
			concat_ws (
				'——',
			CASE
					WHEN "TransPlanType" = 1 THEN       -- 将计算出的内容使用case when 转换，姓名与类型用——隔开
					'贷款签约' 
					WHEN "TransPlanType" = 2 THEN
					'贷款备件' 
				END,
				"HandleBrokerName" 
			),
			';' 
	) AS TypeBrokerNm 
	FROM
		"TransPlanDetail" td 
	WHERE
		"TransPlanId" = T."Id" 
		AND "HandleBrokerId" IS NOT NULL 
	) 交易人员 
FROM
	"DeliverCases" d
	INNER JOIN "TransPlans" T ON d."Id" = T."DeliverCaseId" 
WHERE
	d."StatusCd" = 'finishCase' 
	AND (d."JYCode" = 'JY202108250003' OR d."JYCode" = 'JY202108090002')