报表导出sql

用了毕生所学，写出来的sql，记录一下，留作纪念

SELECT
	(
				select string_agg(unit_name, '-->') as unit_chain from ( with recursive cte (id,unit_name) as (
        select *, 1 as depath from t_unit where id = (select unit_id from t_role where id = t3.role_id) union all
        select b.*,depath+1 from t_unit b inner join cte c on b.id=c.parent_id and b.deleted = 0 and c.deleted = 0)
        select id,unit_name from cte WHERE cte.deleted = 0 ORDER BY depath desc) as t2 
	
	) as 组织机构,
	t1.id as 房源id,
  t6.real_name as 收房客户经理,
	t2.building_name as 小区名称,
	t1.building_no || '-' || t1.unit_no || '-' || room_no as 门牌号,
	t3.rent_per_month as 收房月租金,
	to_char(to_timestamp(t8.final_rent_day/1000),'yyyy-MM-dd') as 起租日,
	to_char(to_timestamp(t4.start_date/1000),'yyyy-MM-dd') as 首次租赁开始时间,
	t4.price as 首次出房租金,
	CASE t4.pay_method
		WHEN 1 THEN '月付'
		WHEN 2 THEN '季付'
		WHEN 3 THEN '半年付'
		WHEN 4 THEN '年付'	
	END as 出房付款方式,
	t7.real_name as 出房客户经理,
	(t4.start_date - final_rent_day)/1000/3600/24 as 空置时间,
	(CAST(t4.price as numeric) - CAST(t3.rent_per_month as numeric)) as 租金差额,
CASE  WHEN CAST(t3.rent_per_month as numeric) = 0 THEN
		null
	ELSE
		round(((CAST(t3.rent_per_month as numeric) - CAST(t4.price as numeric))/CAST(t3.rent_per_month as numeric) * 100),2) || '%'
END as 亏损率

FROM
	t_housing t1
LEFT JOIN t_building t2 on t1.community_id = t2.id
LEFT JOIN t_trust t3 on t3.housing_id = t1.id
LEFT JOIN (

select t1.* from t_contract_rent t1 INNER JOIN (select house_id,min(create_time) as create_time from t_contract_rent GROUP BY house_id) as t2 on t1.house_id = t2.house_id and t1.create_time = t2.create_time
) as t4 on t4.house_id = t1.id
LEFT JOIN t_user t6 on t6.id = t3.deal_user
LEFT JOIN t_user t7 on t7.id = t4.saler_id
LEFT JOIN (
select 
id,
CASE  WHEN rent_day is not null  THEN
		rent_day
	ELSE
		CASE trust_type
	WHEN 1 THEN
		(trust_start_time)/1000 + (fitment_period * 30 * 24 * 3600)
	ELSE
		(trust_start_time)/1000
	END 
end as final_rent_day
from t_trust 
) as t8 on t8.id = t3.id