SQL实现：基于左外连接为去重车辆行生成自定义函数值

针对你这个百万级数据量的需求，我优先推荐用EXISTS半连接来实现，它能在保证结果正确的同时，最大限度减少数据库的IO和计算开销，非常适合大表场景。

最优SQL方案 #

SELECT c.id, c.brand,
       EXISTS (
           SELECT 1
           FROM images i
           WHERE i.car_id = c.id
             AND i.url = 'www.example.com'
             AND (i.data->'d'->>'x')::boolean IS TRUE
       ) AS any_value_of_x_true
FROM cars c;

方案说明 #

1. EXISTS半连接的优势：这个子查询会针对每一行cars记录，检查是否存在符合条件的关联images行。和JOIN不同的是，EXISTS在找到第一个匹配的记录后就会停止扫描，不会遍历所有关联行，这在百万级数据下能大幅减少不必要的计算。
2. 去重逻辑：如果cars.id是主键（通常表设计都会把id设为主键），那么SELECT c.id, c.brand本身就不会产生重复行，不需要额外加DISTINCT；如果你的cars表存在id重复的异常情况，再加上DISTINCT即可。

性能优化关键：索引 #

要让这个查询跑起来飞快，必须给images表创建针对性的复合过滤索引：

CREATE INDEX idx_images_car_id_url_x ON images (car_id, url) 
WHERE ((data->'d'->>'x')::boolean IS TRUE);

这个索引的妙处在于：

• 把关联条件car_id和过滤条件url放在索引里，数据库可以直接通过索引定位到目标行
• 附加的WHERE过滤条件只包含x为true的行，进一步缩小了索引的范围，查询时完全不需要访问表的原始数据（覆盖索引效果）

不推荐的方案（仅作对比） #

有些同学可能会想到用LEFT JOIN+GROUP BY的方式实现，但这种方案在百万级数据下性能极差：

SELECT c.id, c.brand,
       MAX(CASE WHEN i.url = 'www.example.com' AND (i.data->'d'->>'x')::boolean IS TRUE THEN TRUE ELSE FALSE END) AS any_value_of_x_true
FROM cars c
LEFT JOIN images i ON c.id = i.car_id
GROUP BY c.id, c.brand;

原因是LEFT JOIN会先把所有关联的images行和cars行拼接成巨大的中间结果集，再通过GROUP BY聚合，中间数据量可能是原表的数倍，IO和内存开销都会飙升，完全不适合大表场景。

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