подзапрос

прошу помощи, т.к. сам не могу справиться, уже десяток вариантов запроса написал, ни один не работает.

1. необходимо с помощью подзапросов вывести квартиры, в которых количество проживающих больше среднего по данному району.
нужные нам поля:
Bulding.Distinct, Bulding.Street, Bulding.House (район, улица, дом)
Owners.Street, Owners.House, Owners.Flat (улица, дом, квартира)
в таблице Owners хранятся жильцы квартир (не обращайте внимание на название таблицы)
т.е. если в квартире живет 3 человека, то есть 3 записи с одинаковыми Street, House, Flat.

вот, например, один из неправильных вариантов запроса:

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, COUNT(*) AS ResidentsNumber
FROM Owners AS O1, Building AS B1
WHERE ((O1.Street=B1.Street) And (O1.House=B1.House))
GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat
HAVING (ResidentsNumber>(SELECT AVG(ResidentsNumber)
FROM Building AS B2
WHERE B2.Distinct=B1.Distinct
GROUP BY B2.Distinct;));

при попытке выполнения требует ввести ResidentsNumber, ничего не выводит


1+. вот этот запрос возвращает количество жильцов для каждой отдельной квартиры:

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, COUNT(*) AS ResidentsNumber
FROM Owners AS O1, Building AS B1
WHERE ((O1.Street=B1.Street) And (O1.House=B1.House))
GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat;

вопрос. почему, если добавить условие (ResidentsNumber>2), то запрос не воспринимает опять-таки это дело как новосозданный столбец и спрашивает значение ResidentsNumber?

спасибо.

уточнение. первый запрос ничего не выводит, если ввести значение, не удовлетворяющее условию ResidentsNumber>... Если удовлетворяющее, то выводит все записи. Судя по всему, AVG возвращает правильное значение.

автор

SELECT AVG(ResidentsNumber)

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

автор

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, COUNT(*) AS ResidentsNumber FROM Owners AS O1, Building AS B1 WHERE ((O1.Street=B1.Street) And (O1.House=B1.House)) GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat; вопрос. почему, если добавить условие (ResidentsNumber>2), то запрос не воспринимает опять-таки это дело как новосозданный столбец и спрашивает значение ResidentsNumber?

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, COUNT(*) AS ResidentsNumber
FROM Owners AS O1, Building AS B1
WHERE ((O1.Street=B1.Street) And (O1.House=B1.House))
GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat
HAVING COUNT(*) > 2

да, я это подозревал и пытался. например,

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, COUNT(*) AS ResidentsNumber
FROM Owners AS O1, Building AS B1
WHERE ((O1.Street=B1.Street) And (O1.House=B1.House))
GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat
HAVING (COUNT (*)>(SELECT AVG(SELECT COUNT(*)
FROM Owners AS O2, Building AS B2
WHERE ((O2.Street=B2.Street) And (O2.House=B2.House))
GROUP BY B2.Distinct, B2.Street, B2.House, O2.Flat)
FROM Building AS B3
WHERE B3.Distinct=B1.Distinct
GROUP BY B3.Distinct;));

говорит: "Данный подчиненный запрос должен возвращать не более одной записи."
SELECT COUNT, что ли? и зачем мне AVG от одного значения?

Попробуй разбить один запрос на два. Сначала отладь одну часть, потом вставь ее в другой запрос, но ни как текст SQL, а выбрав из списка в построителе запросов.

Я тоже мучился с запросомм с групповыми операциями, но натыкался на ограничение Access по связям нескольких полей.

на вскид - точки с запятыми явно лишние покрайне мере в одном месте

ILL HEAD

на вскид - точки с запятыми явно лишние покрайне мере в одном месте

наверное глупость сморозил. хотя непонятно как он их терпит
вот это WHERE B3.Distinct=B1.Distinct GROUP BY B3.Distinct множит строки позапроса

Вообще слово Distinct зарезервированное в SQL языке, ИМХО использование в навании поля нежелательно.

1. я почему-то думал, точку с запятой надо ставить после каждого селекта. access не ругается. собственно, считая так, одной точки с запятой не хватает...

2. слово distinct тут не мешает, а если иногда и мешает, то достаточно заключить его в квадратные скобки.

3. как так множит?

а вот еще вариант :)

SELECT B1.Distinct, B1.Street, B1.House, O1.Flat, Count(*) AS ResidentsNumber
FROM Owners AS O1, Building AS B1
WHERE (((O1.Street)=[B1].[Street]) AND ((O1.House)=[B1].[House]))
GROUP BY B1.Distinct, B1.Street, B1.House, O1.Flat
HAVING ((AVG(SELECT COUNT(*)
FROM Owners AS O2, Building AS B2
WHERE ((O2.Street=B2.Street) And (O2.House=B2.House) And (B2.Distinct=B1.Distinct))
GROUP BY B2.Distinct, B2.Street, B2.House, O2.Flat;))<=(COUNT(*)));