SQL вложена заявка

Имам таблица с изображения и таблица с местоположения. Искам да извлека списък с изображения, които са най-новите изображения за всяко местоположение в определени граници.

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


ruby-on-rails sql nested activerecord
person standup75    schedule 17.02.2011    source източник
comment
@standup75 Редактирах въпроса ви, за да го изясня, според вашите коментари, но нямам време да пренапиша отговора сега. Късмет.   -  person Nikita Rybak    schedule 17.02.2011
comment
Има ли колона в изображенията на таблицата, която показва датата/часа, когато изображението е добавено/създадено? В противен случай как се определя колко скорошно е дадено изображение?   -  person rskar    schedule 17.02.2011
comment
Често отговарян въпрос. Например, stackoverflow.com/questions/1140064/   -  person pilcrow    schedule 17.02.2011
comment
Заради ефективността бих предложил да използвате EXISTS вместо IN.   -  person Darth Continent    schedule 17.02.2011
comment
@Darth Continent EXISTS не би работил тук. Той получава комплект документи за самоличност   -  person Matthew    schedule 17.02.2011

Отговори (2)


arrow_upward
0
arrow_downward

Ще трябва да използвате псевдоними и агрегиране в подзаявка.

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)
person rskar    schedule 17.02.2011
comment
С помощта на ActiveRecord намерих: Image.where(:location_id =› Location.where(latitude › ? AND latitude ‹ ? AND longitude › ? AND longitude ‹ ?, 17.954, 52.574, -107.392, -64.853)).where(:created_at = › Image.group(:location_id).maximum(:created_at).values) - person standup75; 18.02.2011

arrow_upward
0
arrow_downward 

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)

Трябва да използвате Топ 1 подреждане по вашата дата desc, в случай че имате 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)
    AND I.Id = 
        (
            SELECT Top 1 I2.Id 
            FROM Images I2 
            WHERE I2.Location_Id = L.Id 
            ORDER BY Updated_At DESC
        )
person user617850    schedule 17.02.2011