选择1到n的关系“错误的方式”

huangapple
go评论116阅读模式
英文:

Select 1 to n relationship "the wrong way arround"

问题

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

  1. SELECT o.orderId AS id, o.address,
  2.        json_group_array(
  3.            json_object(
  4.                'productid', p.productid,
  5.                'price', p.price,
  6.                'amount', op.amount
  7.            )
  8.        ) AS items
  9. FROM orders AS o
  10. JOIN orderedProducts AS op ON o.orderId = op.orderId
  11. JOIN products AS p ON op.productid = p.productid
  12. GROUP BY o.orderId, o.address;

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

英文:

products:

productid price
0 10
1 20

orders:

orderId address
0 lala
1 lala

orderedProducts:

productid orderId amount
0 0 1
1 0 2
1 1 2

I want to get:

  1. orders[
  2.    {id, address, items[{productid , price, amount}, {productid , price, amount}]},
  3.    {id, address, items[{productid , price, amount}]}
  4. ]

Is that possible with a single query? I am using SQLite with Python.

答案1

得分: 0

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

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

这里有一个示例 SQL Fiddle。

英文:

You can use GROUP_CONCAT, something like this:

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

Here's an example SQL Fiddle

答案2

得分: 0

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

您的查询可以如下所示：

  1. SELECT
  2.   o.id AS order_id,
  3.   o.address,
  4.   json_group_array(
  5.     json_object(
  6.       'productid', p.id,
  7.       'price', p.price,
  8.       'amount', op.amount
  9.     )
  10.   ) AS items
  11. FROM
  12.   orders o
  13. LEFT JOIN
  14.   orderedProducts op ON o.id = op.orderId
  15. LEFT JOIN
  16.   products p ON op.productid = p.id
  17. GROUP BY
  18.   o.id, o.address;

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

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

模拟数据创建：

  1. CREATE TABLE products (
  2.   id SERIAL PRIMARY KEY,
  3.   price INTEGER
  4. );
  6. CREATE TABLE orders (
  7.   id SERIAL PRIMARY KEY,
  8.   address VARCHAR(100)
  9. );
  11. CREATE TABLE orderedProducts (
  12.   productid INTEGER REFERENCES products(id),
  13.   orderId INTEGER REFERENCES orders(id),
  14.   amount INTEGER
  15. );
  17. INSERT INTO products (id, price) VALUES (0, 10), (1, 20);
  18. INSERT INTO orders (id, address) VALUES (0, 'lala'), (1, 'lala');
  19. INSERT INTO orderedProducts (productid, orderId, amount) VALUES (0, 0, 1), (1, 0, 2), (1, 1, 2);
英文:

You can use json_group_array and json_object. Documentation for usage is here

Your query could be this:

  1. SELECT
  2.   o.id AS order_id,
  3.   o.address,
  4.   json_group_array(
  5.     json_object(
  6.       'productid', p.id,
  7.       'price', p.price,
  8.       'amount', op.amount
  9.     )
  10.   ) AS items
  11. FROM
  12.   orders o
  13. LEFT JOIN
  14.   orderedProducts op ON o.id = op.orderId
  15. LEFT JOIN
  16.   products p ON op.productid = p.id
  17. GROUP BY
  18.   o.id, o.address;

You have to use GROUP BY o.id, o.address because of the json_group_array

To make some demo queries feel free to use this site.

Mock data creation:

  1. CREATE TABLE products (
  2.   id SERIAL PRIMARY KEY,
  3.   price INTEGER
  4. );
  6. CREATE TABLE orders (
  7.   id SERIAL PRIMARY KEY,
  8.   address VARCHAR(100)
  9. );
  11. CREATE TABLE orderedProducts (
  12.   productid INTEGER REFERENCES products(id),
  13.   orderId INTEGER REFERENCES orders(id),
  14.   amount INTEGER
  15. );
  17. INSERT INTO products (id, price) VALUES (0, 10), (1, 20);
  18. INSERT INTO orders (id, address) VALUES (0, 'lala'), (1, 'lala');
  19. INSERT INTO orderedProducts (productid, orderId, amount) VALUES (0, 0, 1), (1, 0, 2), (1, 1, 2);

huangapple
  • 本文由 发表于 2023年6月29日 18:45:27
  • 转载请务必保留本文链接：https://go.coder-hub.com/76580298.html
  • python
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定