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

Question

我有一个表，其中包含数组中的记录。还有另一个表有单个字符串记录。我想获取在另一个表中多次出现的记录。以下是表格；

车辆

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;

这只计算出现在另一个表的两个或多个记录中的车辆名称。例如，它不会拾取出现两次或更多次的同名车辆记录。