查找在另一个表数组(postgres)中多次出现的记录

Question

我有一张列有记录的桌子。此外，还有另一个具有单个字符串记录的表。我想得到在另一个表中多次出现的记录。下表如下：

Vehicle

veh_id |             vehicle_types              
-------+---------------------------------------
    1  | {"byd_tang","volt","viper","laferrari"} 
    2  | {"volt","viper"}                        
    3  | {"byd_tang","sonata","jaguarxf"}        
    4  | {"swift","teslax","mirai"}              
    5  | {"volt","viper"}                        
    6  | {"viper","ferrariff","bmwi8","viper"}   
    7  | {"ferrariff","viper","viper","volt"}  

vehicle_names

    id |  vehicle_name
  -----+-----------------------
    1  |  byd_tang
    2  |  volt
    3  | viper
    4  | laferrari
    5  | sonata
    6  |  jaguarxf
    7  |  swift
    8  |  teslax
    9  | mirai
    10 | ferrariff
    11 | bmwi8

我有一个查询，它可以提供我所期望的输出，但它不是最优的，而且可能是它昂贵的查询。

这是一个查询：

select veh_name 
from vehicle_names dsb
where (select count(*) from vehicle dsd
       where dsb.veh_name = ANY (dsd.veh_types)) > 1

产出应是：

 byd_tang
 volt
 viper

Answer 1

一个选项是聚合查询：

SELECT
   vn.id,
   vn.veh_name
FROM vehicle_names vn
INNER JOIN vehicle v
   ON vn. veh_name = ANY (v.veh_types)
GROUP BY
  vn.id,
  vn.veh_name
HAVING
  COUNT(*) > 1;

这仅计算出现在另一个表中两个或多个记录中的车辆名称。例如，它不会收集到同一名称的单一车辆记录出现两次或更多次。