PostgreSQL自连接：按条件新增MERCHANT与GROUP列的实现问题

表结构与测试数据 #

CREATE TABLE accounts (
  "id" INTEGER,
  "parent_account" INTEGER,
  "merchant_type" VARCHAR(8),
  "name" VARCHAR(32)
);

INSERT INTO accounts
  ("id", "parent_account", "merchant_type", "name")
VALUES
  (1, 14056, 'outlet', 'RAA CHA SUKI & BBQ NIPAH MAL'),
  (2, 14056, 'outlet', 'RAA CHA SUKI & BBQ SUNTER MALL'),
  (3, 14056, 'outlet', 'RAA CHA SUKI & BBQ BAYWALK PLUIT'),
  (3499, NULL, 'MERCHANT', 'Kopi Kotak'),
  (3500, 3499, 'OUTLET', 'Kopi Kotak Tebet'),
  (14052, NULL, 'GROUP', 'Champ Group'),
  (14056, 14052, 'MERCHANT', 'RAA CHA');

业务规则 #

• 若parent_account为NULL，该账户无关联商户/集团；
• 若merchant_type为outlet且存在parent_account，则parent_account指向MERCHANT类型账户；
• 若merchant_type为MERCHANT且存在parent_account，则parent_account指向GROUP类型账户。

现有查询的问题 #

• Query #1：ID为14056的MERCHANT账户，关联的「Champ Group」错误出现在merchant列；
• Query #2：ID为14056的MERCHANT账户，GROUP列值为NULL，未正确关联到对应集团。

正确的自连接查询方案 #

通过两次自连接分别关联商户和集团层级，结合merchant_type做条件匹配，确保各层级关联正确：

SELECT
  a.id,
  a.parent_account,
  a.merchant_type,
  a.name,
  -- 匹配商户名称：outlet取父级MERCHANT的名称，MERCHANT取自身名称，GROUP无商户
  CASE
    WHEN a.merchant_type = 'outlet' THEN m.name
    WHEN a.merchant_type = 'MERCHANT' THEN a.name
    ELSE NULL
  END AS merchant,
  -- 匹配集团名称：outlet取父级MERCHANT的父级GROUP名称，MERCHANT取父级GROUP名称，GROUP取自身名称
  CASE
    WHEN a.merchant_type = 'outlet' THEN g.name
    WHEN a.merchant_type = 'MERCHANT' THEN g.name
    WHEN a.merchant_type = 'GROUP' THEN a.name
    ELSE NULL
  END AS "GROUP"
FROM accounts a
-- 关联MERCHANT层级：仅匹配outlet的父级MERCHANT账户
LEFT JOIN accounts m ON a.parent_account = m.id AND m.merchant_type = 'MERCHANT'
-- 关联GROUP层级：分三种情况匹配
LEFT JOIN accounts g ON 
  (a.merchant_type = 'outlet' AND m.parent_account = g.id) 
  OR (a.merchant_type = 'MERCHANT' AND a.parent_account = g.id)
  OR (a.merchant_type = 'GROUP' AND a.id = g.id)
ORDER BY a.id;

预期查询结果 #

执行上述SQL后，各账户的merchant和GROUP列将完全符合业务规则：

• ID 1-3的outlet：merchant为「RAA CHA」，GROUP为「Champ Group」；
• ID 3499的MERCHANT：merchant为「Kopi Kotak」，GROUP为NULL；
• ID 3500的OUTLET：merchant为「Kopi Kotak」，GROUP为NULL；
• ID 14052的GROUP：merchant为NULL，GROUP为「Champ Group」；
• ID 14056的MERCHANT：merchant为「RAA CHA」，GROUP为「Champ Group」。

内容的提问来源于stack exchange，提问作者Yogyakartas