最近在适配个MySQL应用的项目，各种SQL改成PG兼容的语法真的是脑壳痛，今天遇到个有意思的案例。
原 MySQL SQL语句：
SELECT
DISTINCT
l.MALL_NAME '项目',
t.CONT_NO '合同编号',
t.COMPANY_NAME '租户',
t.STORE_NOS '铺位号',
(select GROUP_CONCAT(r.FLOOR_NAME SEPARATOR ',') from bfbfbf r where r.id in (
select s.FLOOR_ID from bsssb s where s.id in (
select substring_index(substring_index(t1.store_ids, ',', b.help_topic_id + 1), ',', -1) a from bc t1
left join mysql.help_topic b on b.help_topic_id < (length(t1.store_ids) - length(replace(t1.store_ids, ',', '')) + 1)
where t1.CONT_NO = t.CONT_NO)
)) '楼层',
t.BRAND_NAME '品牌',
l1.DICT_NAME '一级业态',
l2.DICT_NAME '二级业态',
l3.DICT_NAME '三级业态',
t.LAYOUT_NAME '签约业态',
t.SHARE_SQUARE '套内面积',
t.RENT_SQUARE '计租面积',
t.STRUCTURE_SQUARE '建筑面积',
t.CONT_BEGIN_DATE A,
IFNULL( CONT_FAIL_DATE, CONT_END_DATE ) B,
CASE
t.`STATUS`
WHEN '0' THEN
'未签约'
WHEN '1' THEN
'已审核'
WHEN '2' THEN
'已解约'
WHEN '3' THEN
'待审核'
WHEN '5' THEN
'已结算' ELSE '未知类型'
END '状态',
(SELECT RENT_TYPE FROM bccdq e where e.CONT_NO = t.CONT_NO and e.IS_DEL = '0' and e.RENT_TYPE is not null order by e.CREATED_DATE desc limit 1) '计租方式',
(select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202301' and m1.CONT_NO = t.CONT_NO limit 1) '2023年1月销售额',
(select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202301' and m1.CONT_NO = t.CONT_NO limit 1) '2023年1月销售笔数',
(select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202302' and m1.CONT_NO = t.CONT_NO limit 1) '2023年2月销售额',
(select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202302' and m1.CONT_NO = t.CONT_NO limit 1) '2023年2月销售笔数',
(select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202303' and m1.CONT_NO = t.CONT_NO limit 1) '2023年3月销售额',
(select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202303' and m1.CONT_NO = t.CONT_NO limit 1) '2023年3月销售笔数',
(select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202304' and m1.CONT_NO = t.CONT_NO limit 1) '2023年4月销售额',
(select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202304' and m1.CONT_NO = t.CONT_NO limit 1) '2023年4月销售笔数',
(select CONFIRM_AMOUNT from msamsuo m1 where SALE_YM = '202305' and m1.CONT_NO = t.CONT_NO limit 1) '2023年5月销售额',
(select CONFIRM_COUNT from msamsuo m1 where SALE_YM = '202305' and m1.CONT_NO = t.CONT_NO limit 1) '2023年5月销售笔数',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-01' and IS_DEL = '0' limit 1) '2023年1月固定租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-02' and IS_DEL = '0' limit 1) '2023年2月固定租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-03' and IS_DEL = '0' limit 1) '2023年3月固定租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-04' and IS_DEL = '0' limit 1) '2023年4月固定租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '01' and FINANCE_PERIOD = '2023-05' and IS_DEL = '0' limit 1) '2023年5月固定租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-01' and IS_DEL = '0' limit 1) '2023年1月提成租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-02' and IS_DEL = '0' limit 1) '2023年2月提成租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-03' and IS_DEL = '0' limit 1) '2023年3月提成租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-04' and IS_DEL = '0' limit 1) '2023年4月提成租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '1010' and FINANCE_PERIOD = '2023-05' and IS_DEL = '0' limit 1) '2023年5月提成租金',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-01' and IS_DEL = '0' limit 1) '2023年1月物业管理费',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-02' and IS_DEL = '0' limit 1) '2023年2月物业管理费',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-03' and IS_DEL = '0' limit 1) '2023年3月物业管理费',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-04' and IS_DEL = '0' limit 1) '2023年4月物业管理费',
(select RECE_AMOUNT from FIRCVWZZZZ e where e.cont_no = t.cont_no and e.FEE_TYPE = '02' and FINANCE_PERIOD = '2022-05' and IS_DEL = '0' limit 1) '2023年5月物业管理费'
FROM
bc t
LEFT JOIN bms l ON l.id = t.MALL_ID
LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 4 ) l4 ON l4.ID = t.LAYOUT
LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 3 ) l3 ON ( l3.ID = t.LAYOUT OR l4.PARENT_ID = l3.ID )
LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 2 ) l2 ON ( l2.ID = t.LAYOUT OR l3.PARENT_ID = l2.ID )
LEFT JOIN ( SELECT * FROM bsggldddict WHERE `LEVEL` = 1 ) l1 ON l2.PARENT_ID = l1.ID
WHERE
l.MALL_TYPE = '1'
and t.IS_DEL = '0'
AND t.STATUS IN ( 1, 2, 5 )
AND t.CONT_BEGIN_DATE <= '2023-05-31' AND IFNULL( CONT_FAIL_DATE, CONT_END_DATE ) >= '2023-01-01';
这条SQL是首先改成postgresql 兼容的语法，其实要改的内容不多，只有一个标量子查询处需要改写：
(select GROUP_CONCAT(r.FLOOR_NAME SEPARATOR ',') from bfbfbf r where r.id in (
select s.FLOOR_ID from bsssb s where s.id in (
select substring_index(substring_index(t1.store_ids, ',', b.help_topic_id + 1), ',', -1) a from bc t1
left join mysql.help_topic b on b.help_topic_id < (length(t1.store_ids) - length(replace(t1.store_ids, ',', '')) + 1)
where t1.CONT_NO = t.CONT_NO)
)) '楼层',
这里开发很聪明，使用了个小技巧使用 help_topic_id 递增的 help_topic_id 字段 + 进行 GROUP_CONCAT 函数进行行转列。
postgresql 由于没有 mysql.help_topic 表、GROUP_CONCAT、substring_index 这类函数所以需要等价改写上面的逻辑。
洽洽是这条SQL花了我几个小时来进行逻辑的等价改写，脑壳疼得厉害，还费烟，不过尝试了N次，查了不少函数终于给我改写出来了
标签：rows,postgresql,..,NO,适配,time,改写,text,CONT From： https://www.cnblogs.com/yuzhijian/p/17586235.html