json 连接销售订单上的行项目的最佳方法

tv6aics1 于 2022-12-15 发布在其他

关注(0)|答案(2)|浏览(136)

我有一个可能是补救性的问题，但我很难通过搜索找到实施该问题的最佳方法。我正在处理一个重组工作流，该工作流将获取最新发运的销售订单并发送电子邮件。我正在查询的数据库具有“销售订单-〉行项目”关系。
简化示例：销售订单表
| 销售订单ID|客户|售出日期|发货日期|
| - ------|- ------|- ------|- ------|
| 1个|客户1| 2022年7月12日|2022年7月12日|
| 第二章|客户1| 2022年7月12日|2022年7月12日|
| 三个|客户2| 2022年7月12日|2022年7月12日|
销售订单上的行
| 行项目ID|行号|销售订单ID|产品|价格|
| - ------|- ------|- ------|- ------|- ------|
| 1个|1个|1个|鸡蛋|1美元|
| 第二章|第二章|1个|牛奶|1美元|
| 三个|1个|第二章|面包|1美元|
| 四个|第二章|第二章|鸡蛋|1美元|
| 五个|三个|第二章|牛奶|1美元|
| 六个|1个|三个|鸡蛋|1美元|
我不确定将行连接到销售订单的最佳方法。显然，我不认为简单地连接数据真的是我想要的。

SELECT * FROM SalesOrder
JOIN lines ON SalesOrder.SalesOrderID = Lines.SalesOrderID
WHERE SalesOrderID = 2

会产生如下结果（不要认为这是正确的实现方式）：
| 销售订单ID|客户|售出日期|发货日期|行项目ID|行号|销售订单ID|产品|价格|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 第二章|客户2| 2022年7月12日|2022年7月12日|三个|1个|第二章|面包|1美元|
| 第二章|客户2| 2022年7月12日|2022年7月12日|四个|第二章|第二章|鸡蛋|1美元|
| 第二章|客户2| 2022年7月12日|2022年7月12日|五个|三个|第二章|牛奶|1美元|
最后，我尝试将此数据Carbone.io从Retool Workflow输入www.example.com，以便使用工作流生成PDF销售订单，我认为该工作流需要是JSON，如下所示

"SalesOrderID": 2,
"Customer": "Customer2",
"SoldDate": "12/7/22",
"LineItems": {ArrayOfLineItems}

我尝试过选择所有销售订单，然后使用工作流循环迭代每个订单，为销售订单中的每一行选择所有行项目，这为我提供了每个销售订单的行项目，但我似乎不知道如何在同一个JSON中获得行项目和销售订单。
尝试编写一个查询，将所有行作为JSON数组包含在一列中，这样是否更好？
很抱歉长的职位，但感谢任何反馈或建议的最佳方式来尝试这一点！

JSON

来源：https://stackoverflow.com/questions/74733919/best-way-to-join-line-items-on-sales-order

2条答案

按热度按时间

jq6vz3qz1#

您可以使用以下命令直接在Oracle中获取JSON：

SELECT JSON_OBJECT(
         KEY 'salesOrderId' VALUE s.salesOrderId,
         KEY 'customer'     VALUE s.customer,
         KEY 'soldDate'     VALUE s.soldDate,
         KEY 'shipDate'     VALUE s.shipDate,
         KEY 'lineItems'    VALUE l.lineItems FORMAT JSON
         RETURNING CLOB
       ) AS salesorder
FROM   salesorder s
       CROSS JOIN LATERAL (
         SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    KEY 'lineItemId' VALUE l.lineItemid,
                    KEY 'lineNumber' VALUE l.lineNumber,
                    KEY 'product'    VALUE l.product,
                    KEY 'price'      VALUE l.price
                  )
                  ORDER BY l.lineNumber
                  RETURNING CLOB
                ) AS lineItems
         FROM   lines l
         WHERE  l.salesorderid = s.salesorderid
       ) l

其中，对于示例数据：

CREATE TABLE SalesOrder (SalesOrderID, Customer, SoldDate, ShipDate) AS
SELECT 1,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 2,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 3,   'Customer2', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL;

CREATE TABLE Lines (LineItemID, LineNumber, SalesOrderID, Product, Price) AS
SELECT 1,   1,  1,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 2,   2,  1,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 3,   1,  2,  'Bread', 1.00 FROM DUAL UNION ALL
SELECT 4,   2,  2,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 5,   3,  2,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 6,   1,  3,  'Eggs',  1.00 FROM DUAL;

输出：
| 销售人员|
| - ------|
| {“销售订单ID”：1，“客户”：“客户1”，“销售日期”：“2022-07- 12 T00：00：00”，“发货日期”：“2022-07- 12 T00：00：00”，“行项目”：[{“行项目ID”：1，“行号”：1，“产品”：“鸡蛋”，“价格”：1}，{“行项目ID”：2，“行号”：2，“产品”：“牛奶”，“价格”：1}]}|
| {“销售订单ID”：2，“客户”：“客户1”，“销售日期”：“2022-07- 12 T00：00：00”，“发货日期”：“2022-07- 12 T00：00：00”，“行项目”：[{“行项目ID”：3，“行号”：1，“产品”：“面包”，“价格”：1}，{“行项目ID”：4，“行号”：2，“产品”：“鸡蛋”，“价格”：1}，{“行项目ID”：5，“行号”：3，“产品”：“牛奶”，“价格”：1}]}|
| {“销售订单ID”：3，“客户”：“客户2”，“销售日期”：“2022-07- 12 T00：00：00”，“发货日期”：“2022-07- 12 T00：00：00”，“行项目”：[{“行项目ID”：6，“行号”：1，“产品”：“鸡蛋”，“价格”：1}]}|
fiddle

赞(0）回复(0）举报 2022-12-15

jfewjypa2#

像这样？

SELECT * FROM SalesOrder
JOIN Lines1 ON SalesOrder.SalesOrderID = Lines1.SalesOrderID and Lines1.LineItemId= 1
LEFT OUTER JOIN Lines2 ON SalesOrder.SalesOrderID = Lines2.SalesOrderID and Lines2.LineItemId= 2
LEFT OUTER JOIN Lines3 ON SalesOrder.SalesOrderID = Lines3.SalesOrderID and Lines3.LineItemId= 3

这是stuff函数，可能会让你更接近你想要的格式。

select t.*
,(SELECT
        DISTINCT
            STUFF((select ',' + l.Product
                    FROM LineTable l
                    WHERE l.SalesOrderId = t.SalesOrderId
                    FOR XML path('')),1,1,'') as 'Product'
    ) 
from SalesTable t

赞(0）回复(0）举报 2022-12-15

我来回答

json 连接销售订单上的行项目的最佳方法

2条答案

相关问题

热门标签

最新问答