SELECT t0.票据信息UUID AS PJXXUUID,
t2.FJ AS FJ,
t0.行程信息UUID AS HCXXUUID
FROM (SELECT t0.行程信息UUID AS 行程信息UUID,
t0.报销UUID AS 报销UUID,
t0.报销类型 AS 报销类型,
t0.交通工具 AS 交通工具,
t0.出发日期 AS 出发日期,
t0.起点 AS 起点,
t0.到达日期 AS 到达日期,
t0.终点 AS 终点,
t0.单据张数 AS 单据张数,
t0.合计金额 AS 合计金额,
t0.票据信息UUID AS 票据信息UUID,
t0.录入人 AS 录入人,
t0.录入日期 AS 录入日期,
t0.创建时间 AS 创建时间,
t0.修改时间 AS 修改时间,
t0.修改人 AS 修改人,
t0.报销金额 AS 报销金额
FROM (SELECT t0.HCXXUUID AS 行程信息UUID,
t0.BXUUID AS 报销UUID,
t0.BXLX AS 报销类型,
t0.JTGJ AS 交通工具,
t0.CFRQ AS 出发日期,
t0.QD AS 起点,
t0.DDRQ AS 到达日期,
t0.ZD AS 终点,
t0.DJZS AS 单据张数,
t0.HJJE AS 合计金额,
regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
1) AS 票据信息UUID,
t0.LRR AS 录入人,
t0.LRRQ AS 录入日期,
t0.CREATE_TIME AS 创建时间,
t0.UPDATE_TIME AS 修改时间,
t0.XGR AS 修改人,
t0.BXJE AS 报销金额
FROM szhfpgl.fact_bx_clxcmxb t0
UNION ALL
SELECT t0.HCXXUUID AS 行程信息UUID,
t0.BXUUID AS 报销UUID,
t0.BXLX AS 报销类型,
t0.JTGJ AS 交通工具,
t0.CFRQ AS 出发日期,
t0.QD AS 起点,
t0.DDRQ AS 到达日期,
t0.ZD AS 终点,
t0.DJZS AS 单据张数,
t0.HJJE AS 合计金额,
regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
2) AS 票据信息UUID,
t0.LRR AS 录入人,
t0.LRRQ AS 录入日期,
t0.CREATE_TIME AS 创建时间,
t0.UPDATE_TIME AS 修改时间,
t0.XGR AS 修改人,
t0.BXJE AS 报销金额
FROM szhfpgl.fact_bx_clxcmxb t0
WHERE (regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
2) IS NOT NULL)
UNION ALL
SELECT t0.HCXXUUID AS 行程信息UUID,
t0.BXUUID AS 报销UUID,
t0.BXLX AS 报销类型,
t0.JTGJ AS 交通工具,
t0.CFRQ AS 出发日期,
t0.QD AS 起点,
t0.DDRQ AS 到达日期,
t0.ZD AS 终点,
t0.DJZS AS 单据张数,
t0.HJJE AS 合计金额,
regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
3) AS 票据信息UUID,
t0.LRR AS 录入人,
t0.LRRQ AS 录入日期,
t0.CREATE_TIME AS 创建时间,
t0.UPDATE_TIME AS 修改时间,
t0.XGR AS 修改人,
t0.BXJE AS 报销金额
FROM szhfpgl.fact_bx_clxcmxb t0
WHERE (regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
3) IS NOT NULL)
UNION ALL
SELECT t0.HCXXUUID AS 行程信息UUID,
t0.BXUUID AS 报销UUID,
t0.BXLX AS 报销类型,
t0.JTGJ AS 交通工具,
t0.CFRQ AS 出发日期,
t0.QD AS 起点,
t0.DDRQ AS 到达日期,
t0.ZD AS 终点,
t0.DJZS AS 单据张数,
t0.HJJE AS 合计金额,
regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
4) AS 票据信息UUID,
t0.LRR AS 录入人,
t0.LRRQ AS 录入日期,
t0.CREATE_TIME AS 创建时间,
t0.UPDATE_TIME AS 修改时间,
t0.XGR AS 修改人,
t0.BXJE AS 报销金额
FROM szhfpgl.fact_bx_clxcmxb t0
WHERE (regexp_substr(REPLACE(REPLACE(t0.PJXXUUID, '[', ''),
']',
''),
'[^,]+',
1,
4) IS NOT NULL)) t0
WHERE (t0.行程信息UUID = 'c1ce79a5ef6746d78fb3e995694f22f6' AND
t0.行程信息UUID IN
('4efe8a806c8b42db8db94cddb769c9dd',
'c1ce79a5ef6746d78fb3e995694f22f6'))) t0
LEFT JOIN (SELECT t0.票据信息UUID AS 票据信息UUID
FROM (SELECT t0.票据信息UUID AS 票据信息UUID,
t0.发票代码 AS 发票代码,
t0.发票号码 AS 发票号码,
t0.票据类别 AS 票据类别,
t0.票据创建时间 AS 票据创建时间,
t0.录入人 AS 录入人,
t0.录入日期 AS 录入日期,
t0.报销类型 AS 报销类型,
t0.修改人 AS 修改人,
t0.项目名称 AS 项目名称,
t0.开票日期 AS 开票日期,
t0.税额 AS 税额,
t0.购方名称 AS 购方名称,
t0.购方识别号 AS 购方识别号,
t0.销方识别号 AS 销方识别号,
t0.销方名称 AS 销方名称,
t0.数据来源 AS 数据来源,
t0.金额 AS 金额,
t0.插入时间 AS 插入时间,
t0.任务上传UUID AS 任务上传UUID,
t0.修改时间 AS 修改时间,
t0.时间 AS 时间,
t1.DEPT_ID AS 所属部门
FROM (SELECT t0.pjxxuuid AS 票据信息UUID,
t0.fpdm AS 发票代码,
t0.fphm AS 发票号码,
t0.pjlx AS 票据类别,
t0.create_time AS 票据创建时间,
t0.lrr AS 录入人,
t0.lrrq AS 录入日期,
t0.bxlx AS 报销类型,
t0.xgr AS 修改人,
t0.xmmc AS 项目名称,
t0.kprq AS 开票日期,
t0.se AS 税额,
t0.gfmc AS 购方名称,
t0.gfsbh AS 购方识别号,
t0.xfsbh AS 销方识别号,
t0.xfmc AS 销方名称,
t0.sjly AS 数据来源,
t0.je AS 金额,
t0.crsj AS 插入时间,
t0.rwscuuid AS 任务上传UUID,
t0.update_time AS 修改时间,
t0.SJ AS 时间
FROM szhfpgl.fact_pj_ocrsbjbxxhzb t0
UNION ALL
SELECT t0.PJXXUUID AS 票据信息UUID,
t0.FPDM AS 发票代码,
t0.FPHM AS 发票号码,
t0.PJMX AS 票据类型,
t0.create_time AS 创建时间,
t0.LRR AS 录入人,
t0.LRRQ AS 录入日期,
t0.BXLX AS 报销类型,
t0.XGR AS 修改人,
CASE
WHEN t0.BZ IS NULL THEN
'该票据未填写备注'
ELSE
t0.BZ
END AS 备注,
date_format(t0.KPRQ, '%Y%m%d') AS 开票日期,
t0.HJSE AS 税额,
null AS 购方名称,
null AS 购方识别号,
null AS 销方识别号,
null AS 销方名称,
'x-manual input' AS 数据来源,
cast(t0.JSHJ AS decimal(30, 8)) AS 价税合计,
NULL AS 插入时间,
null AS 任务上传UUID,
t0.UPDATE_TIME AS 修改时间,
null AS 时间
FROM szhfpgl.fact_pj_typjxxb t0) t0
LEFT JOIN qsn.SZSYS_4_USERS t1
ON (t0.录入人 = t1.USER_ID)) t0
LEFT JOIN (SELECT t0.PJXXUUID AS 票据信息UUID,
row_number() OVER(partition by t0.PJXXUUID ORDER BY t0.PJXXUUID) AS RN
FROM szhfpgl.fact_pj_fj t0) t1
ON (t0.票据信息UUID = t1.票据信息UUID)
AND (t1.RN = 1)
LEFT JOIN (SELECT t0.报销UUID AS 报销UUID,
t0.票据信息UUID AS 票据信息UUID
FROM (SELECT t0.BXUUID AS 报销UUID,
t0.PJXXUUID AS 票据信息UUID
FROM szhfpgl.fact_bx_bxsxmxb_cf t0
UNION ALL
SELECT t0.BXUUID AS 报销UUID,
t0.PJXXUUID AS 票据信息UUID
FROM szhfpgl.fact_bx_clxcmxb_cf t0) t0) t2
ON (t0.票据信息UUID = t2.票据信息UUID)) t1
ON (t0.票据信息UUID = t1.票据信息UUID)
LEFT JOIN szhfpgl.fact_pj_fj t2
ON (t0.票据信息UUID = t2.PJXXUUID)
ORDER BY t0.票据信息UUID -- limit 10000
上段SQL的结果集的行数是随机性的1行，或者234行，基表数据不变的情况下