时间戳

首页 > 解决方案 > 在 PostgreSQL 上互连表

postgresql - 在 PostgreSQL 上互连表

问题描述

我是这里的新手。

我正在使用 PostgreSQL 来处理我特定研究领域的大量数据。不幸的是，我遇到了一个不允许我继续分析的问题。我试图简化我的问题以清楚地说明它。

假设我有一个名为“Buyers”的表，其中包含这些数据： table_buyers

买家只能在每家商店购买一次或不购买。一共有三间店，每间都有一张桌子。就像下面这样：

table_store1

table_store2

table_store3

要创建表，我使用以下代码：

CREATE TABLE public.buyer
(
ID integer NOT NULL PRIMARY KEY,
name text NOT NULL, 
phone text NOT NULL
) 
WITH (
  OIDS = FALSE
)
;

CREATE TABLE public.Store1
(
ID_buyer integer NOT NULL PRIMARY KEY,
total_order numeric NOT NULL, 
total_itens integer NOT NULL
) 
WITH (
  OIDS = FALSE
)
;


CREATE TABLE public.Store2
(
ID_buyer integer NOT NULL PRIMARY KEY,
total_order numeric NOT NULL, 
total_itens integer NOT NULL
) 
WITH (
  OIDS = FALSE
)
;


CREATE TABLE public.Store3
(
ID_buyer integer NOT NULL PRIMARY KEY,
total_order numeric NOT NULL, 
total_itens integer NOT NULL
) 
WITH (
  OIDS = FALSE
)
;

要在表格上添加信息，我使用以下代码：

INSERT INTO buyer (ID, name, phone) VALUES
    (1, 'Alex', 88888888),
    (2, 'Igor', 77777777),
    (3, 'Mike', 66666666);

INSERT INTO Store1 (ID_buyer, total_order, total_itens) VALUES
    (1, 87.45, 8),
    (2, 14.00, 3),
    (3, 12.40, 4);

INSERT INTO Store2 (ID_buyer, total_order, total_itens) VALUES
    (1, 785.12, 7),
    (2, 9874.21, 25);

INSERT INTO Store3 (ID_buyer, total_order, total_itens) VALUES
    (2, 45.87, 1);

由于所有表都通过买方的 ID 相互连接，我希望我可以有一个查询来生成如下所示的输出： 所需的输出表。

请注意，如果买家没有在商店购买任何东西，我必须打印“0”。

我知道这是一项容易的任务，但不幸的是，我一直未能完成它。

使用“AND”逻辑运算符，我尝试了以下代码来完成此任务：

SELECT 
  buyer.id, 
  buyer.name, 
  store1.total_order, 
  store2.total_order, 
  store3.total_order
FROM 
  public.buyer, 
  public.store1, 
  public.store2, 
  public.store3
WHERE 
  buyer.id = store1.id_buyer AND
  buyer.id = store2.id_buyer AND
  buyer.id = store3.id_buyer;

但是，很明显，它只是返回了“Igor”，因为这是唯一一个在所有三个商店都购买过商品的买家（打印屏幕）。

然后，我尝试了“OR”逻辑运算符，就像下面的代码：

SELECT 
  buyer.id, 
  buyer.name, 
  store1.total_order, 
  store2.total_order, 
  store3.total_order
FROM 
  public.buyer, 
  public.store1, 
  public.store2, 
  public.store3
WHERE 
  buyer.id = store1.id_buyer OR
  buyer.id = store2.id_buyer OR
  buyer.id = store3.id_buyer;

但随后，它返回 12 行错误值（打印屏幕）。

显然，我的错误是没有考虑到“买家”不必在我的代码上的所有三个商店。我自己无法纠正，请您帮帮我吗？

我非常感谢一个可以照亮我的答案。非常感谢！

关于如何搜索此问题的提示也非常受欢迎！

标签： postgresql

解决方案

行。我怀疑这是你的最终答案，但它是一个开始

SELECT 
  buyer.id, 
  buyer.name, 
  COALESCE( gb_store1.total_orders, 0 ) as store1_total, 
  COALESCE( gb_store2.total_orders, 0 ) as store2_total, 
  COALESCE( gb_store3.total_orders, 0 ) as store3_total
FROM
  public.buyer, 
  LEFT OUTER JOIN ( SELECT ID_buyer, 
                   SUM( total_orders ) as total_orders,
                   SUM( total_itens ) as total_itens
     FROM public.store1
     GROUP BY ID_buyer ) gb_store1 ON gb_store1.id_buyer = buyer.id ,
  LEFT OUTER JOIN ( SELECT ID_buyer, 
                   SUM( total_orders ) as total_orders,
                   SUM( total_itens ) as total_itens
     FROM public.store2
     GROUP BY ID_buyer ) gb_store2 ON gb_store2.id_buyer = buyer.id ,
  LEFT OUTER JOIN ( SELECT ID_buyer, 
                   SUM( total_orders ) as total_orders,
                   SUM( total_itens ) as total_itens
     FROM public.store3
     GROUP BY ID_buyer ) gb_store3 ON gb_store3.id_buyer = buyer.id ;

所以，这个查询有几个元素应该关注。subselects/groupby 允许您在子表中按 ID_buyer 汇总。LEFT OUTER JOIN 使您的查询仍然可以返回结果，即使子选择没有找到匹配的记录。最后，当您的总数之一为 NULL 时，COALESCE 允许您返回 0（因为子选择未找到匹配项）。

希望这可以帮助。

推荐阅读