创建JSON并编辑复杂的查询（Oracle 11g）

Question

我有4个不同的表：

table_price_product（包含与产品及其价格有关的信息）

table_price_list（包含与价目表相关的信息）

prices_per_client（包含与给定特定产品的不同客户的价格相关的信息）

客户（包含与客户相关的信息）

这是我的SQL FIDDLE：LINK

我懂了：

CUSTOMER_NUMBER  |  CUSTOMER_CLASS_CODE|    PRICE
(null)           |           A         |    29223
(null)           |           B         |    33223
112121           |           E         |    40340
119435           |           E         |    40340

现在，我想在一个查询中获得与产品和客户有关的所有数据，分别用CLASS A，CLASS B，CLASS C替换A，B，C等， 但是
如果customer_class_code是=’E’，我想从表 客户端
获取名称，最后转换所有内容，并为每种产品获取JSON。看看这个，以某种方式我需要添加列“
PRICES_FOR_CLIENTS”和“组”，因为我需要这些列来生成JSON。

SKU     |PRICE|PRICES_FOR_CLIENTS|groups|CLASS A|CLASS B|WALMART|SUPERMARKET
99342435|9999 |                  |      |29223  |33223  |40340  |40340

我想使用该信息生成JSON：

{“ sku”：“ 99342435”，“ PRICE”：“ 9999”，PRICES_FOR_CLIENTS：[{“ group”：“ CLASS
A”，“ PRICE”：“ 29223”}，{“ group”：“ CLASS B”，“ PRICE“：” 33223“}，{” group“：”
WALMART“，” PRICE“：” 40340“}，{” group“：” SUPERMARKET“，” PRICE“：” 40340“}]}};

你能帮助我吗？

编辑：

<Item SKU="99342435" Price="9999">
    <PRICES_FOR_CLIENTS>
        <CLIENT_PRICE>
            <Client>WALMART</Client>
            <Price>40340</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>SUPERMARKET</Client>
            <Price>48343</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>B</Client>
            <Price>33223</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>A</Client>
            <Price>29223</Price>
            <Site>USSITE</Site>
        </CLIENT_PRICE>
    </PRICES_FOR_CLIENTS>
</Item>

Answer 1

我认为您可以使用以下查询替换大多数代码。您可能需要调整IN子句，如果要更改很多客户列表，这会很麻烦。但这会复制您的结果：

SELECT *
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, tpp.price AS ITEM_PRICE, ppc.price
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1))
PIVOT (AVG(PRICE) FOR IDENTIFIER IN ('A' AS CLASS_A , 'B' AS CLASS_B, 'SUPERMARKET' AS SUPERMARKET, 'WALMART' AS WALMART));

这是一个更新小提琴。

至于JSON输出，如果您使用的是更高版本，它将变得更加容易，因为它现在已成为核心功能的一部分。

编辑：每个注释添加XML功能

您可以查询以下查询：

SELECT XMLSERIALIZE(CONTENT
                    XMLELEMENT("Item",
                               XMLATTRIBUTES(sub.item_code AS "SKU", sub.item_price AS "Price"),
                               XMLELEMENT("PRICES_FOR_CLIENTS",
                                          XMLAGG(XMLELEMENT("CLIENT_PRICE",
                                                            XMLFOREST(sub.identifier AS "Client", sub.price AS "Price"))))) AS CLOB INDENT)                                              
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, tpp.price AS ITEM_PRICE, avg(ppc.price) AS PRICE
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
      GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price) sub
WHERE sub.identifier IS NOT NULL
GROUP BY sub.item_code, sub.item_price;

这是该查询的更新小提琴（Link）。

产生以下输出：

<Item SKU="99342435" Price="9999">
    <PRICES_FOR_CLIENTS>
        <CLIENT_PRICE>
            <Client>WALMART</Client>
            <Price>40340</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>SUPERMARKET</Client>
            <Price>48343</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>B</Client>
            <Price>33223</Price>
        </CLIENT_PRICE>
        <CLIENT_PRICE>
            <Client>A</Client>
            <Price>29223</Price>
        </CLIENT_PRICE>
    </PRICES_FOR_CLIENTS>
</Item>

编辑2：通过字符串合并添加JSON

以下将通过直接字符串概括输出JSON：

SELECT '{"sku":"'||sub.item_code||'","PRICE":"'||sub.item_price||'",PRICES_FOR_CLIENTS:['||listagg('{"group":"'||sub.identifier||'","PRICE":"'||sub.price||'"}',',') WITHIN GROUP (ORDER BY sub.identifier)||']};' AS JSON                                              
FROM (SELECT DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code) AS IDENTIFIER, tpp.item_code, replace(tpp.price, ',', '.') AS ITEM_PRICE, REPLACE(avg(ppc.price), ',', '.') AS PRICE, 
      tpl.request_id, max(tpl.request_id) over (partition by tpp.item_code) as max_request
      FROM table_price_list tpl
      INNER JOIN table_price_product tpp ON tpp.list_header_id = tpl.list_header_id AND tpp.request_id = tpl.request_id
      INNER JOIN prices_per_client ppc ON tpp.item_code = ppc.item_code
      LEFT JOIN clients c ON ppc.customer_number = c.account_number
      WHERE SYSDATE BETWEEN NVL(tpp.start_date_active, SYSDATE) AND NVL(tpp.end_date_active, SYSDATE+1)
      GROUP BY DECODE(ppc.customer_class_code, 'E', c.description, ppc.customer_class_code), tpp.item_code, tpp.price, tpl.request_id) sub 
WHERE sub.identifier IS NOT NULL
and sub.request_id = sub.max_request
GROUP BY sub.item_code, sub.item_price;

以及与此查询相关的更新小提琴（Link）

编辑3：添加了替换 编辑4：添加了分析功能