如何从相关表中仅选择唯一值 [英] how to select only unique values from related tables

问题描述

我有三个表：

 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d

branch_1
 id | parent_id | b1_name
----+-----------+---------
  1 |         1 | aaa
  2 |         1 | bbb
  3 |         1 | ccc
  4 |         2 | ddd
  5 |         2 | eee
  6 |         2 | fff
  7 |         2 | ggg
  8 |         2 | hhh

branch_2
 id | b2_name | parent_id
----+---------+-----------
  1 | b2_aa   |         1
  2 | b2_ba   |         2
  3 | b2_bb   |         2
  4 | b2_bc   |         2

我希望将这些表仅与唯一值组合在一起。我尝试了此代码：

select p.id, b1.b1_name, b2.b2_name
from parent p
left outer join lateral (select distinct b1.b1_name from branch_1 b1 where b1.parent_id=p.id order by b1.b1_name) as b1 on true
left outer join lateral (select distinct b2.b2_name from branch_2 b2 where b2.parent_id=p.id order by b2.b2_name) as b2 on true

但结果中有重复值：

 id | b1_name | b2_name
----+---------+---------
  1 | aaa     | b2_aa
  1 | bbb     | b2_aa
  1 | ccc     | b2_aa
  2 | ddd     | b2_ba
  2 | ddd     | b2_bb
  2 | ddd     | b2_bc
  2 | eee     | b2_ba
  2 | eee     | b2_bb
  2 | eee     | b2_bc
  2 | fff     | b2_ba
  2 | fff     | b2_bb
  2 | fff     | b2_bc
  2 | ggg     | b2_ba
  2 | ggg     | b2_bb
  2 | ggg     | b2_bc
  2 | hhh     | b2_ba
  2 | hhh     | b2_bb
  2 | hhh     | b2_bc

我想要：

 id | b1_name | b2_name
----+---------+---------
  1 | aaa     | b2_aa
  1 | bbb     | ---
  1 | ccc     | ---
  2 | ddd     | b2_ba
  2 | eee     | b2_bb
  2 | fff     | b2_bc
  2 | ggg     | ---
  2 | hhh     | ---

可能看起来很奇怪，但我只需要唯一的值，不需要重复的值。那么我如何才能唯一值呢？

附注：我需要这样一个表，用于在狮身人面像上进行面搜索，以便正确考虑产品数量，并提出这样的要求：

select * from facetIndex facet b1_name, b2_name

推荐答案

您可以使用窗口函数仅对前几行执行外连接：

select t.id, t.b1_name, b2.b2_name
from (
  select p.id, b1.b1_name, row_number() over (partition by p.id order by b1_name) as rn
  from parent p
    join branch_1 b1 on b1.parent_id = p.id
) t
  left join (
    select parent_id, b2_name, 
           row_number() over (partition by parent_id) as rn
    from branch_2 
  ) b2 on b2.parent_id = t.id and b2.rn = t.rn
order by t.id;

在线示例：https://rextester.com/GNYF31027

这篇关于如何从相关表中仅选择唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！