查询具有复杂关系的多个表 [英] Querying multiple tables with complex relationships

问题描述

我所需要的查询超出了我的SQL知识，因此我希望在这里得到一些帮助。我想在postgreSQL 9.2中将多个表的关系放到一个表中。我知道该过程，但不了解SQL。

The query required for what I'd like to is beyond my SQL knowledge so I'm hoping to get some help here. I want to get releationships from multiple tables into one in postgreSQL 9.2. I know the procedure but I don't know the SQL.

此查询中将包含4个表：

There are 4 tables that will go into this query:

• 愿望清单：与一个或多个列表相关联，并且更喜欢商店


• list_wishlist：保持列表和愿望清单之间的关系


• 项目：：与列表相关联，并且更喜欢商店


• prefered_stores：（这将保持商店与首选商店之间的关系（每个首选商店都是
  preferreded_store中的单独行。因此，如果某个商品或愿望清单具有多个
  首选商店，则preferred_store。这些行的id相同）

• wishlist: is associated with one or more lists and has prefered stores
• list_wishlist: holds the relationship between lists and wishlists
• item: is associated with a list and has prefered stores
• prefered_stores: this holds the relationsship between stores and prefered stores (Each prefered store is a separate row in prefered_store. Thus if an item or wishlist has more than one prefered store, prefered_store.id will be the same for those rows)

这些表格看起来像这样（删除了不相关的列）：

The tables look like this (with irrelevant columns removed):

这是结果表看起来像：

And here is what the resulting table would look like:

让我解释一下结果表：

• item_id：：项目ID


• item_stores_comments：存储/评论
  对与此项目相关联的所有preferred_stores行中（商店/注释用逗号分隔，对/用分号分隔）


• wishlist_stores：关联的收藏清单的preferred_stored的商店ID
  以及该项目所在的列表（以逗号分隔）

• item_id: just that, id of item
• item_stores_comments: store/comment pairs from all prefered_stores rows associated with this item (store/comment separated by comma and pairs separated by semicolon)
• wishlist_stores: store ids of prefered_stored of wishlists that are associated with lists that this item is in (separated by commas)

我已经用实际结果填充了item_info表对于上面的示例表，我想应该很清楚，但是如果您没有得到什么，请告诉我。

I've filled the item_info table with the actual result for the example tables above so I guess it should be clear but please let me know if you don't get something.

我已经按照建议创建了一个SQLFiddle评论： http://sqlfiddle.com/#!12/9fd60
它包含与图像中相同的架构和值。

I've created a SQLFiddle as recommended in the comments: http://sqlfiddle.com/#!12/9fd60 It contains the same schema and values as in the images.

推荐答案

这应该做您想要做的：

WITH a AS (
  SELECT item.id, string_agg(prefered_store.store::varchar, ',') wishlist_stores
  FROM item, list_wishlist, wishlist, prefered_store
  WHERE item.list=list_wishlist.list
    AND list_wishlist.wishlist=wishlist.id
    AND wishlist.prefered_stores=prefered_store.id
  GROUP BY item.id
), b AS (
  SELECT item.id, 
    string_agg(
      prefered_store.store::varchar || ',' || prefered_store.comment,
      ' ; ') item_stores_comments
    FROM item, prefered_store
    WHERE item.prefered_stores=prefered_store.id
    GROUP BY item.id
)
SELECT a.id,item_stores_comments,wishlist_stores 
FROM a,b
WHERE a.id=b.id

这篇关于查询具有复杂关系的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！