创建JSON并编辑复杂的查询(Oracle 11g) [英] Creating JSON and editing a complex query (oracle 11g)
问题描述
我有4个不同的表:
table_price_product(包含与产品相关的信息,以及 价格)
table_price_list(包含与价目表相关的信息)
prices_per_client(包含与以下价格有关的信息: 不同的客户提供了特定的产品)
客户(包含与客户相关的信息)
这是我的SQL字段: 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',我想从表 clients 中获取名称,最后,转换所有内容并为每种产品获取JSON.看看这个,以某种方式我需要添加列"PRICES_FOR_CLIENTS"和"groups",因为我需要这些列来生成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>
我认为您可以使用以下查询替换大部分代码.您可能需要调整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;
这是该查询的更新的小提琴(链接). /p>
哪个会产生以下输出:
<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>
通过字符串合并添加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;
以及与此查询相关的更新的小提琴(链接)
添加了替换 **添加了分析功能**
I have 4 different tables:
table_price_product (contains information related to the products and their price)
table_price_list (contains the information related to the price list)
prices_per_client (contains the information related to the prices for the different clients given a specific product)
clients (contains the information related to the clients)
This is my SQL FIDDLE: LINK
I got this:
CUSTOMER_NUMBER | CUSTOMER_CLASS_CODE| PRICE
(null) | A | 29223
(null) | B | 33223
112121 | E | 40340
119435 | E | 40340
Now, i want to obtain in a single query all the data related to the products and the clients, replace A,B,C etc with CLASS A, CLASS B, CLASS C respectively but if the customer_class_code is = 'E' i want to obtain the name from table clients AND finally, convert everything and get JSONs for every product. Take a look at this, somehow i would need to add column "PRICES_FOR_CLIENTS" and "groups" because i need those columns to generate the JSON.
SKU |PRICE|PRICES_FOR_CLIENTS|groups|CLASS A|CLASS B|WALMART|SUPERMARKET
99342435|9999 | | |29223 |33223 |40340 |40340
I would like to generate a JSON with that information:
{"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"}]};
Can you help me?
EDIT:
<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>
I think you can replace most of your code with the following query. You may need to adjust the IN clause, which is a pain if you are changing the customer list a lot. But this replicates your results:
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));
Here is an update fiddle.
As for the JSON output, it would be much easier if you were on a later version as it is now part of the core functionality.
EDIT: Adding XML Functionality per Comments
You could check out this query:
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;
Here is an updated fiddle with that query (Link).
Which produces the following output:
<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>
Edit 2: Adding JSON via String Concatination
The following would output JSON via direct string concatination:
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;
And an updated fiddle with this query (Link)
Edit 3: Added Replace **Edit 4: Added analytical function **
这篇关于创建JSON并编辑复杂的查询(Oracle 11g)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!