问题描述
每次运行我的代码时，我都会以 JSON 格式获取一行、两行或几行.我使用的是 Oracle 11g.
Every time that I run my code I obtain one, or two or several rows on a JSON format. I am using Oracle 11g.
这是我在 SQL Fiddle 上的代码，你会 在那里找到所有数据.
This is my CODE on SQL Fiddle, you will find all the data there.
例如，在这种情况下，我得到了这样的东西:
For example, in this case I obtained something like this:
{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"沃尔玛","PRICE":"40340"}]};
但我可以根据存储在表中的数据获得此输出:
BUT I could get this output depending on the data stored in the tables:
{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"沃尔玛","PRICE":"40340"}]};
{"sku":"95453343","PRICE":"8778",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"沃尔玛","PRICE":"40340"}]};
{"sku":"95453343","PRICE":"8778",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"WALMART","PRICE":"40340"}]};
我想让我的查询能够在第一行中放置一个左括号"，检查是否存在其他行，如果没有显示新行，则插入一个关闭括号在末尾和一个分号；像这样:
I want to make my query able to put an "open bracket" in the first row, check if there exist another rows and if no new rows are shown then, insert a close bracket in the end and a semicolon; something like this:
[{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"沃尔玛","PRICE":"40340"}]}];
但另一种情况可能是存在超过 1 行；在这种情况下，我想在第一行放置一个左括号，而不是一个右括号.我只想打开第一行的括号并在最后一行和分号内关闭它.不同的行必须用逗号分隔.请遵循以下示例:
BUT another scenario could be when there exist more than 1 row; in that case I would like to put an open bracket in the first row but not a close bracket. I only want to open the bracket in the first row and close it in the last row and within, a semicolon. The different rows MUST BE separated by a comma. Please, follow this example:
[{"sku":"99342435","PRICE":"9999",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"沃尔玛","PRICE":"40340"}]},
{"sku":"95453343","PRICE":"8778",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"WALMART","PRICE":"40340"}]}];
{"sku":"95453343","PRICE":"8778",PRICES_FOR_CLIENTS:[{"group":"A","PRICE":"29223"},{"group":"B","PRICE":"33223"},{"group":"SUPERMARKET","PRICE":"48343"},{"group":"WALMART","PRICE":"40340"}]}];
我尝试了很多次，但我无法做到.
I tried so many times but I was not able to do it.
你能帮我吗?
推荐答案
这开始落入以下领域:仅仅因为您可以使其工作，并不意味着您应该这样做.但是要回答您的问题，请查看此代码:
This is starting to fall into the realm of just because you can make it work, doesn't mean you should. But to answer your question, check this code out:
SELECT CASE WHEN sub2.TOTAL_ROW > 1 AND sub2.this_row = 1 THEN '[' ELSE NULL END||
sub2.json||
CASE WHEN sub2.total_row > 1 AND sub2.this_row = sub2.total_row THEN ']' ELSE NULL END AS JSON
FROM (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,
COUNT(*) OVER () AS TOTAL_ROW,
ROW_NUMBER() OVER (ORDER BY sub.item_code, sub.item_price) AS THIS_ROW
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) sub2
ORDER BY sub2.this_row;
我更新了你的 SQLFiddle
这个查询的作用是扩展上一个查询.它使用分析函数 COUNT 和 ROW_NUMBER 计算总行数和这一行.我根本没有对它们进行分区，因为我们希望它们考虑所有返回的行.如果总行数 > 1 且这是第一行，则以["开头.在中间附加 JSON.如果这是最后一行(Total > 1 and ROW_NUMBER = COUNT)，则将]"附加到末尾.
What this query does is expand on the previous query. It counts the total rows and this row using the analytic functions COUNT and ROW_NUMBER. I didn't partition them at all because we want them to consider all returned rows. If Total Rows > 1 and this is the first row start it with '['. Append the JSON in the middle. And if this is the last row (Total > 1 and ROW_NUMBER = COUNT) then append ']' to the end.
编辑 1:更改为始终有括号
SELECT DECODE(sub2.this_row, 1, '[', NULL)||
sub2.json||
DECODE(sub2.this_row, sub2.total_row, ']', NULL) AS JSON
FROM (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,
COUNT(*) OVER () AS TOTAL_ROW,
ROW_NUMBER() OVER (ORDER BY sub.item_code, sub.item_price) AS THIS_ROW
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) sub2
ORDER BY sub2.this_row;
这是更新后的 SQLFiddle(链接).
Here is the updated SQLFiddle (Link).
考虑到更简单的逻辑，我将 CASE 换成了 DECODE.如果该行是第一行，它将获得左括号.如果是最后一行，则获取右括号.如果两者兼而有之，则两者兼而有之.
Given the simpler logic, I switched out the CASE for DECODE. IF the row is the first row it gets the opening bracket. If it is the last row, it gets the closing bracket. If it is both, it gets both.
这篇关于编辑输出(根据检索的数量和分号设置括号)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！