Внутреннее соединение с первым результатом


В SQL Server имеется две таблицы: домов и их изображений.

Мне нужен список с 20 домов с первыми их изображений (только один). Я попробовал:

SELECT top 20 h.id, h.name, im.id, im.name
        FROM image im 
        INNER JOIN house h ON im.house_id = h.id
        WHERE 1=1 AND im.id=(SELECT TOP (1) im2.id FROM image im2 WHERE im.id=im2.id ORDER BY image_code)

но это работает очень медленно. Есть ли способ улучшить этот запрос?

Редактировать:

С запросом:

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM     _house h, _image im              --  Tables in join
WHERE    h.id = im.id_house            --  The join (equivalent to inner join)
GROUP BY h.id                          --  This compresses all entries with the
                                       --  same h.id into a single row 
HAVING   im.id =  min(im.id)           --  This is how we select across a group
                                       --  (thus compressing the image table per house)

Я получаю сообщение об ошибке:

В верхней части страницы.ИД' недопустим в предложении having, потому что оно не содержится ни в статистической функции или группы by пункт.

Затем, я изменение:

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM     _house h, _image im              --  Tables in join
WHERE    h.id = im.house_id            --  The join (equivalent to inner join)
GROUP BY h.id,im.id, h.name, im.name                          --  This compresses all entries with the
                                       --  same h.id into a single row 
HAVING   im.id =  min(im.id)   

И тогда я получаю такой результат:

enter image description here

Как я могу убрать повторяющиеся значения?

EDIT2:

Если кто-то хочет проверить запросы, это скрипт для создания таблиц и данных, которые я использую сейчас (реальные данные около 1 млн. строк):

CREATE TABLE _house(
    [id] [int] NOT NULL,
    [name] [varchar](50) NULL
) 


CREATE TABLE _image(
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [house_id] [int] NULL
) 

insert into _house (id, name) values (1,'house1');
insert into _house (id, name) values (2,'house2');

insert into _image (id, name, house_id) values (31,'img1',1);
insert into _image (id, name, house_id) values (32,'img2',2);
insert into _image (id, name, house_id) values (33,'img3',2);
insert into _image (id, name, house_id) values (34,'img4',2);


26824
2
задан 23 сентября 2011 в 12:09 Источник Поделиться
Комментарии
3 ответа

Я не знаю, если есть более быстрый способ, но я хотел бы использовать подзапросы. Например:

select top 20 h.id, h.name, im.mid, i.name
from _house h
join
(
select min(id) as mid,house_id from _image
group by house_id
) im on im.house_id=h.id
join _image i on i.id=im.mid

В зависимости от контекста это может быть быстрее, чтобы создать временную таблицу с помощью всего одного изображения для каждого дома.

5
ответ дан 27 сентября 2011 в 03:09 Источник Поделиться

Вы должны использовать пункт Группы

SELECT   h.id, h.name, im.id, im.name  --  What you want to select
FROM house h,image im -- Tables in join
WHERE h.id = im.house_id -- The join (equivalent to inner join)

GROUP BY h.id -- This compresses all entries with the
-- same h.id into a single row
HAVING min(im.id) -- This is how we select across a group
-- (thus compressing the image table per house)

LIMIT 20; -- Selecting the first n values is very
-- DB specific on mysql use the limit clause
-- But I see in your DB it is `top 20`

Примечание:

По этой странице: http://developer.mimer.com/validator/parser200x/index.tml#parser

Предложение having более стандартным при указании такой (хотя я не могу проверить это).

HAVING   im.id = min(im.id)

Редактирование (на основе редактировать вопрос).

Ваша проблема в этой строке:

GROUP BY h.id, im.id, h.name, im.name 

Это означает, что для каждой строки должен быть уникальным для всех четырех значений будут сжаты вместе (т. е. если все четыре значения такие же линии будут сжаты). Вы должны сохранить первоначальную группу по пункту (и исправить другую часть запроса).

GROUP BY h.id

Я не могу проверить это так как у меня только MySQL и представляется с помощью MS продукта (и мой первоначальный запрос работал на MySQL). Но исходя из сообщения об ошибке:


*В верхней части страницы.ИД' недопустим в предложении having, потому что оно не содержится ни в статистической функции или группы by пункт.*

Мы не хотим добавлять что-либо к группе пункта. Таким образом, следующее сообщение об ошибке указывает, что мы должны использовать агрегатные функции (выбор возможно).

Попробуйте изменить выберите:

SELECT   h.id, h.name, min(im.id), im.name 
^^^^^^^^^^

Я уверен, если вы играете вокруг с этим вы должны быть в состоянии заставить его работать. К сожалению я не могу быть более точным, но это потребует используя тот же продукт, что и вы.

5
ответ дан 24 сентября 2011 в 10:09 Источник Поделиться

Группа по X - сворачивает все строки с одинаковым значением х в одном ряду. Ваш запрос от _house ч, в верхней части страницы им ... группа по ч. идентификатор не является правильным, потому что он не говорит, что делать с верхней части страницы.

Из _house ч, в верхней части страницы им ... группа по ч. ИД им.идентификатор ч. имя им.имя - это не то, что вы хотите, потому что держит все возможные комбинации сек.ИД им.идентификатор ч. имя, и им.имя, но не все можно им строк, только строки, где им.идентификатор является минимальным значением.

Вы хотите свернуть все строки в верхней части страницы с таким же house_id, или группы по house_id. Затем для каждой из этих строк вы хотите минимальные идентификатор:

SELECT house_id, Min(id) FROM _image GROUP BY house_id

Что дает вам минимум в верхней части страницы.идентификатор для каждого house_id. Теперь, если вы хотите найти _house.название , которое имеет этот минимум ID, вам нужно присоединится к house_id против _house.ИД. Вы могли бы поставить предыдущий запрос во временную таблицу и присоединиться против этого, но я считаю, что SQL-сервер позволяет объединить против подвыборки:

SELECT h.id, h.name, mi.minImageId
FROM _house h
JOIN (SELECT house_id, Min(id) AS minImageId
FROM _image GROUP BY house_id) mi ON mi.house_id = h.id

Я дал мин(идентификатор) имя, потому что мы собираемся нуждаться в этом позже. Вы хотите найти имя в верхней части страницы подряд с минимальным идентификатор для каждой строки в группе подзапроса. Вы не хотите, чтобы положить, что в вашей группе подзапроса выборки, потому что это, опять же, включает в себя все возможные названия. Вы только хотите, чтобы имя в верхней части страницы подряд с минимальным ID, который мы сейчас знаем и назвали minImageId. Присоединение этого подзапроса против это должно дать вам то, что вы хотите:

SELECT h.id, h.name, mi.minImageId, i.name
FROM _house h
JOIN (SELECT house_id, Min(id) AS minImageId
FROM _image GROUP BY house_id) mi ON mi.house_id = h.id
JOIN _image i ON i.id = mi.minImageId

2
ответ дан 30 сентября 2011 в 12:09 Источник Поделиться