如何在SQL查询结果中重复指定数据表的统计数据？

用户需求 #

按账户从3个不同数据表中获取最大日期并将其并列展示，已为每个表编写单独查询，通过UNION ALL合并结果后使用PIVOT处理，希望让其中2个表的查询结果重复显示，询问该需求是否可行。

附用户当前SQL代码：

--define var_ent_type = 'ACOM' 
--define var_ent_id = '52766'
--define var_dict_id = 113

SELECT
*
FROM 
(
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY' as "TableName",
    PS.DICTIONARY_ID,
    to_char(MAX(PS.END_EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN PERFORMDBO.PERF_SUMMARY PS ON (PS.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND PS.DICTIONARY_ID >= 100
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY',
    PS.DICTIONARY_ID

union all 

SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(H.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN HOLDINGDBO.POSITION H ON (H.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION',
    1

union all 

SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(C.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN CASHDBO.CASH_ACTIVITY C ON (C.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY',
    1

--ORDER BY
--    2,3, 4 
) 

PIVOT

(
MAX("MaxDate")
FOR "TableName"
IN ('CASH_ACTIVITY', 'PERF_SUMMARY','POSITION')
)

可行性与实现方案 #

该需求完全可行，核心实现逻辑如下：

• 在UNION ALL阶段，为需要重复显示的表生成多组不同的TableName标识（比如给POSITION新增POSITION_2，给CASH_ACTIVITY新增CASH_ACTIVITY_2）
• 在PIVOT的IN子句中添加这些新增的标识，即可让同一表的最大日期以多列形式重复展示

修改后的SQL代码示例（以重复POSITION和CASH_ACTIVITY为例） #

--define var_ent_type = 'ACOM' 
--define var_ent_id = '52766'
--define var_dict_id = 113

SELECT
*
FROM 
(
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY' as "TableName",
    PS.DICTIONARY_ID,
    to_char(MAX(PS.END_EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN PERFORMDBO.PERF_SUMMARY PS ON (PS.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND PS.DICTIONARY_ID >= 100
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'PERF_SUMMARY',
    PS.DICTIONARY_ID

union all 

-- 原POSITION查询
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(H.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN HOLDINGDBO.POSITION H ON (H.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION',
    1

union all 

-- 重复POSITION查询，TableName改为POSITION_2
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION_2' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(H.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN HOLDINGDBO.POSITION H ON (H.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'POSITION_2',
    1

union all 

-- 原CASH_ACTIVITY查询
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(C.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN CASHDBO.CASH_ACTIVITY C ON (C.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY',
    1

union all 

-- 重复CASH_ACTIVITY查询，TableName改为CASH_ACTIVITY_2
SELECT
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY_2' as "TableName",
    0 as DICTIONARY_ID,
    to_char(MAX(C.EFFECTIVE_DATE), 'YYYY-MM-DD') as "MaxDate"
FROM
    RULESDBO.ENTITY E    
    INNER JOIN CASHDBO.CASH_ACTIVITY C ON (C.ENTITY_ID = E.ENTITY_ID)
WHERE
    1=1
--    AND E.ENTITY_TYPE = '&var_ent_type'
--    AND E.ENTITY_ID = '&var_ent_id'
    AND (E.ACTIVE_STATUS <> 'N' )--and E.TERMINATION_DATE is null )
GROUP BY
    E.ENTITY_TYPE,
    E.ENTITY_ID,
    'CASH_ACTIVITY_2',
    1

--ORDER BY
--    2,3, 4 
) 

PIVOT

(
MAX("MaxDate")
FOR "TableName"
IN ('CASH_ACTIVITY', 'CASH_ACTIVITY_2', 'PERF_SUMMARY','POSITION', 'POSITION_2')
)

说明 #

• 上述代码中，通过重复添加POSITION和CASH_ACTIVITY的查询语句，仅修改TableName的取值，就能在最终结果中得到这两个表最大日期的重复列
• 如果需要更多次重复，只需继续添加对应表的查询并修改TableName，同时在PIVOT的IN子句中补充新的标识即可

内容的提问来源于stack exchange，提问作者user19874068