问题

你想要的结果似乎是从多个表中组合数据的嵌套结构。在SQLite中，你可以使用子查询和连接来实现这一点。以下是可能的SQL查询示例，以获取你所需的结果：

SELECT o.orderId AS id, o.address,
       json_group_array(
           json_object(
               'productid', p.productid,
               'price', p.price,
               'amount', op.amount
           )
       ) AS items
FROM orders AS o
JOIN orderedProducts AS op ON o.orderId = op.orderId
JOIN products AS p ON op.productid = p.productid
GROUP BY o.orderId, o.address;

这个查询将从 orders、orderedProducts 和 products 表中联接数据，并按订单分组。每个订单将包含一个 id、address 和一个包含订购的产品的嵌套JSON数组 items。这应该与你所描述的所需结果非常接近。

orders[
   {id, address, items[{productid , price, amount}, {productid , price, amount}]},
   {id, address, items[{productid , price, amount}]}
]

答案1

得分: 0

你可以使用 GROUP_CONCAT，类似这样：

SELECT   
  o.orderId,   
  o.address,  
  ''['' || GROUP_CONCAT('{"id": ' || p.productid || ', "price": ' || p.price || ', "amount": ' || op.amount || '}', ', ') || '']'' as items  
FROM orders o  
JOIN orderedProducts op ON o.orderId = op.orderId  
JOIN products p ON op.productid = p.productid  
GROUP BY o.orderId, o.address;  

这里有一个示例 SQL Fiddle。

SELECT   
  o.orderId,   
  o.address,  
  '[' || GROUP_CONCAT('{"id": ' || p.productid || ', "price": ' || p.price || ', "amount": ' || op.amount || '}', ', ') || ']' as items  
FROM orders o  
JOIN orderedProducts op ON o.orderId = op.orderId  
JOIN products p ON op.productid = p.productid  
GROUP BY o.orderId, o.address;  

答案2

得分: 0

您可以使用 json_group_array 和 json_object。有关用法的文档在 此处

您的查询可以如下所示：

SELECT
  o.id AS order_id,
  o.address,
  json_group_array(
    json_object(
      'productid', p.id,
      'price', p.price,
      'amount', op.amount
    )
  ) AS items
FROM
  orders o
LEFT JOIN
  orderedProducts op ON o.id = op.orderId
LEFT JOIN
  products p ON op.productid = p.id
GROUP BY
  o.id, o.address;

由于使用了 json_group_array，您需要使用 GROUP BY o.id, o.address

要创建一些演示查询，可以使用这个 网站。

模拟数据创建：

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price INTEGER
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  address VARCHAR(100)
);

CREATE TABLE orderedProducts (
  productid INTEGER REFERENCES products(id),
  orderId INTEGER REFERENCES orders(id),
  amount INTEGER
);

INSERT INTO products (id, price) VALUES (0, 10), (1, 20);
INSERT INTO orders (id, address) VALUES (0, 'lala'), (1, 'lala');
INSERT INTO orderedProducts (productid, orderId, amount) VALUES (0, 0, 1), (1, 0, 2), (1, 1, 2);

SELECT
  o.id AS order_id,
  o.address,
  json_group_array(
    json_object(
      'productid', p.id,
      'price', p.price,
      'amount', op.amount
    )
  ) AS items
FROM
  orders o
LEFT JOIN
  orderedProducts op ON o.id = op.orderId
LEFT JOIN
  products p ON op.productid = p.id
GROUP BY
  o.id, o.address;

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price INTEGER
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  address VARCHAR(100)
);

CREATE TABLE orderedProducts (
  productid INTEGER REFERENCES products(id),
  orderId INTEGER REFERENCES orders(id),
  amount INTEGER
);

INSERT INTO products (id, price) VALUES (0, 10), (1, 20);
INSERT INTO orders (id, address) VALUES (0, 'lala'), (1, 'lala');
INSERT INTO orderedProducts (productid, orderId, amount) VALUES (0, 0, 1), (1, 0, 2), (1, 1, 2);