sql - 带有多个选择语句的oracle中的LISTAGG

Question

我有以下查询

SELECT LISTAGG(contract, ',')
   WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
      FROM Site_TAB
      WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'

输出如下图

我有另一个查询是

SELECT contract
                   FROM company_site_cfv
                   WHERE cf$_site_type_db = 'CENTRAL'

输出如下。

有什么简单的方法可以写这个，以便我可以得到最终输出为 908,918,901？

任何帮助深表感谢

标签： sqloracle

Answer 1

您可以尝试以下方法：

1. 使用前合并两个查询LISTAGG

SELECT 
    LISTAGG(contract, ',')
        WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
FROM (
    SELECT contract 
    FROM Site_TAB
    WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'
    UNION ALL
    SELECT contract
    FROM company_site_cfv
    WHERE cf$_site_type_db = 'CENTRAL'
) t

2. 由于示例中所示的两个查询都产生了一条记录。您可以 CONCAT 交叉连接的结果，例如

SELECT
    t1."CONTRACT_LIST" || "," || t2.contract AS "CONTRACT_LIST"
FROM
    (
    SELECT LISTAGG(contract, ',')
         WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
    FROM Site_TAB
    WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'
) t1
CROSS JOIN (
    SELECT contract
    FROM company_site_cfv
    WHERE cf$_site_type_db = 'CENTRAL'
) t2

让我知道这是否适合您。