如何编写SQL查询获取坐标组下已接受或最高金额的offer记录

可以通过UNION ALL将两类查询结果合并——因为状态为accepted(3)的记录和非accepted的组内最高金额记录不会重叠，用UNION ALL比UNION性能更优。

完整SQL语句如下：

-- 第一部分：获取所有状态为accepted的offer
SELECT offers.id
FROM offers
WHERE state = 3

UNION ALL

-- 第二部分：获取每个经纬度组中金额最高的非accepted状态的offer
SELECT DISTINCT offers.id 
FROM "offers" 
  INNER JOIN "addresses" ON "addresses"."id" = "offers"."address_id" 
  INNER JOIN (
     SELECT a.longitude, a.latitude, MAX(amount) AS total 
     FROM offers 
      INNER JOIN addresses a ON a.id = offers.address_id 
     WHERE state != 3 
     GROUP BY a.longitude, a.latitude
  ) AS sorted_offers ON sorted_offers.longitude = addresses.longitude 
                    AND sorted_offers.latitude = addresses.latitude 
                    AND sorted_offers.total = offers.amount 

补充说明 #

1. 第一部分直接筛选state=3的offer，逻辑简洁高效
2. 第二部分保留你原本的查询逻辑，仅获取非accepted状态下、每个经纬度组中金额最高的offer
3. 若需要获取offer的完整字段而非仅id，将SELECT offers.id替换为SELECT offers.*（或指定所需字段）即可

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