SQL嵌套查询

Question

我有一个图像表和一个位置表 我想检索图像列表，这些图像是特定边界内每个位置的最新图像。

SELECT * FROM images 
    WHERE location_id IN 
       (SELECT id FROM locations 
            WHERE latitude > 17.954 AND latitude < 52.574 
            AND longitude > -107.392 AND longitude < -64.853)

这是使用嵌套查询，但我们可以通过连接实现相同的目的。 如果我们想要每个位置的所有图像，则此方法有效，但我只想获取每个位置 1 个图像（最新的）

以下是这些表的主要字段，

  table "images"
    integer  "id"
    text     "image_name"
    text     "caption"
    integer  "location_id"
    datetime "created_at"
    datetime "updated_at"
    integer  "view_count"

  table "locations"
    integer  "id"
    text     "name"
    float    "longitude"
    float    "latitude"
    datetime "created_at"
    datetime "updated_at"
    string   "city"
    string   "address"
    string   "province"
    string   "country"
    string   "post_code"

您知道吗？

如果有办法使用 Rails activerecord API 来做到这一点，那就加分了

Answer 1

您将需要在子查询中使用别名和聚合。

SELECT * FROM images IMG
    WHERE location_id IN
       (SELECT id FROM locations
           WHERE latitude > 17.954 AND latitude < 52.574
           AND longitude > -107.392 AND longitude < -64.853)
    AND created_at IN
       (SELECT MAX(created_at) FROM images IMG2
              WHERE IMG2.location_id=IMG.location_id)

Answer 2

SELECT *
FROM Images I     
INNER JOIN Location L On L.Id = I.Location_ID
WHERE (L.latitude Between 17.954 And 52.574)              
    AND (L.longitude Between -107.392 And -64.853)

如果您在同一位置有 2 个具有相同更新日期的日期，则应按日期描述使用前 1 排序

SELECT *
FROM Images I     
INNER JOIN Location L On L.Id = I.Location_ID
WHERE (L.latitude Between 17.954 And 52.574)              
    AND (L.longitude Between -107.392 And -64.853)
    AND I.Id = 
        (
            SELECT Top 1 I2.Id 
            FROM Images I2 
            WHERE I2.Location_Id = L.Id 
            ORDER BY Updated_At DESC
        )