编辑输出(根据检索的数量和分号设置括号)

Question

每次运行我的代码时，我都会获得JSON格式上的一行或两行或几行。我使用的是Oracle11g。

这是我在SQL上的代码，会在那里找到所有的数据。

例如，在本例中，我获得了如下内容：

{"sku":"99342435"，“价格”：“9999”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，“价格”：“40340}}；

但是，根据存储在表中的数据，我可以得到这个输出：

{"sku":"99342435"，“价格”：“9999”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，“价格”：“40340}}； {"sku":"95453343"，“价格”：“8778”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，“价格”：“40340}}；

我想使我的查询能够在第一行中放置一个“开括号”，检查是否还有其他行，如果没有显示新行，则在末尾插入一个近括号和一个分号；如下所示：

[{"sku":"99342435"，“价格”：“9999”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，"PRICE":"40340"}}];

但是，另一种情况可能是当存在超过1行时；在这种情况下，我希望在第一行中放置一个开括号，而不是一个近距括号。我只想打开第一行中的括号，并在最后一行和分号内关闭它。不同的行必须用一个逗号分隔为。请遵循这个例子：

[{"sku":"99342435"，“价格”：“9999”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，“价格”：“40340}}， {"sku":"95453343"，“价格”：“8778”，PRICES_FOR_CLIENTS：{“组”：“A”，“价格”：“29223”}，{“组”：“B”，“价格”：“33223”}，{“组”：“超市”，“价格”：“48343”}，{“组”：“沃尔玛”，“"PRICE":"40340"}}];”

我试过很多次了，但我没能做到。

你能帮帮我吗?

Answer 1

这开始进入你能让它工作的领域，并不意味着你应该这样做。但是要回答你的问题，请查看下面的代码：

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。如果这是最后一行(道达尔>1和ROW_NUMBER = COUNT)，那么将'‘追加到末尾。

编辑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 (链接)。

考虑到更简单的逻辑，我把CASE换成了DECODE。如果该行是第一行，则将得到开始括号。如果是最后一行，则得到结束括号。如果两者兼而有之，两者兼得。

Answer 2

处理此问题的最简单方法是对现有查询的结果执行另一个LISTAGG，并将其包含在[和]中。

SELECT '[' || LISTAGG(JSON, ',') WITHIN GROUP(ORDER BY 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                                              
  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
) j

基于SQLFiddle的演示

请注意，需要将PRICES_FOR_CLIENTS括在双引号中，否则查询的输出是无效的JSON。此外，还需要通过原始查询从字符串输出的末尾删除;。