FoxClub Forums > FAQ FoxPro > Выбрать из дочерней таблицы записи с максимальной датой

:: Список тем • Новая Тема • Поиск • Войти

Выбрать из дочерней таблицы записи с максимальной датой

Дата: 15.06.05 21:08:23

Задача

Есть 2 таблицы (главная и подчиненная) связанные отношением один-ко-многим. Одно из полей подчиненной таблицы - это дата. Необходимо для каждой записи главной таблицы выбрать одну запись из подчиненной таблицы с максимальным значением даты. Т.е. не просто определить максимальную дату, а выбрать всю запись с этой максимальной датой

Решение

Исходные данные


* Главная таблица
CREATE CURSOR tabMain (tabMainID I, NickName C(50))  
INSERT INTO tabMain (tabMainID, NickName) VALUES (1, "Первая запись главной таблицы")  
INSERT INTO tabMain (tabMainID, NickName) VALUES (2, "Вторая запись главной таблицы")  
INSERT INTO tabMain (tabMainID, NickName) VALUES (3, "Третья запись главной таблицы")  

* Подчиненная таблица
* Для первой записи главной таблицы есть дублирующее значение по максимальной дате
* Для второй записи главной таблицы вообще нет значений в подчиненной таблице
CREATE CURSOR tabChild (tabChildID I, tabMainID I, CurDate D, NickName C(50))  
INSERT INTO tabChild (tabChildID, tabMainID, CurDate, NickName) ;
    VALUES (1, 1, DATE(2005,6,15), "Первая запись дочерней первой записи главной")
INSERT INTO tabChild (tabChildID, tabMainID, CurDate, NickName) ;
    VALUES (2, 1, DATE(2005,6,15), "Вторая запись дочерней первой записи главной")
INSERT INTO tabChild (tabChildID, tabMainID, CurDate, NickName) ;
    VALUES (3, 1, DATE(2005,6,10), "Третья запись дочерней первой записи главной")
INSERT INTO tabChild (tabChildID, tabMainID, CurDate, NickName) ;
    VALUES (4, 3, DATE(2005,6,13), "Первая запись дочерней третьей записи главной")
INSERT INTO tabChild (tabChildID, tabMainID, CurDate, NickName) ;
    VALUES (5, 3, DATE(2005,6,14), "Первая запись дочерней третьей записи главной")

У функции DATE() можно задавать параметры, начиная с версии Visual FoxPro 6. Для младших версий укажите значение даты другим способом.

Наиболее очевидным кажется решение через поиск максимального значения. Примерно так


SELECT tabMain.*, tabChild.* ;
FROM tabMain ;
INNER JOIN tabChild ON tabMain.tabMainID=tabChild.tabMainID ;
WHERE tabChild.CurDate IN ;
    (SELECT MAX(CurDate) FROM tabChild WHERE tabMainID = tabMain.tabMainID)

Однако если в дочерней таблице есть записи с одинаковым значением даты и эти даты являются максимальными, то в результирующей выборке получим лишние записи. Попробуйте выполнить этот запрос для приведенных исходных данных и вы увидите в чем проблема.

Чтобы исключить отбор подобных "дублей" следует опираться на уникальный идентификатор записи. В данном случае это tabChild.tabChildID. С его помощью следует отделить одну запись от другой с одинаковой датой.

При этом, чтобы задействовать механизмы оптимизации следует отойти от "шаблона" поиска максимального значения. Точнее, следует вспомнить, что "максимальное" означает лишь тот факт, что нет значения больше, чем найденное. Это значит, что функцию MAX() можно заменить на сравнение "больше или меньше".


SELECT tabMain.*, tab2.* ;
FROM tabMain ;
INNER JOIN tabChild tab2 ON tabMain.tabMainID=tab2.tabMainID ;
WHERE NOT EXISTS(SELECT 'x' FROM tabChild ;
        WHERE tab2.tabMainID = tabChild.tabMainID ;
            AND tab2.CurDate < tabChild.CurDate) ;
AND NOT EXISTS(SELECT 'x' FROM tabChild ;
        WHERE tab2.tabMainID = tabChild.tabMainID ;
            AND tab2.CurDate = tabChild.CurDate ;
            AND tab2.tabChildId < tabChild.tabChildId)

Разберем этот запрос подробнее.

Первое объединение по INNER JOIN просто связывает главную и подчиненную таблицу. Получим все записи подчиненной таблицы, соответствующие записям главной таблицы.

Теперь из этих записей надо оставить лишь те, дата в которых максимальная. Т.е. не существует записей (NOT EXISTS), которые также относились бы к той же записи главной таблицы и имели бы дату бОльшую, чем в найденной записи. Именно это и делает первый подзапрос.

А вот что делает второй подзапрос? Он предназначен именно для контроля той ситуации, когда в дочерней таблице могут быть записи с одинаковым значением даты. В данном случае, в случае наличия такой ситуации в выборку попадут только записи, имеющие бОльшее значение идентификатора записи. Но вы можете указать символ "больше", тогда будет отбираться запись имеющая меньшее значение идентификатора записи.

Обратите внимание, что здесь нельзя использовать для сравнения ключевых полей "не равно". Поскольку "не равно" будет справедливо для обоих записей с одинаковой датой.

К сожалению, в Visual FoxPro невозможно объединить эти два подзапроса в один. Впрочем, в таком виде запрос выглядит более наглядно. А если вы уверены, что в вашей таблице не может быть записей с одинаковым значением даты, то просто не включайте второй подзапрос.

В принципе, тот же самый алгоритм можно использовать, если надо найти не максимальную дату, а скажем, максимальную цену. Или максимальную сумму.

Если вы хотите получить в выборке все записи главной таблицы вне зависимости от того, есть ли для них хотя бы одна запись в подчиненной таблице, то достаточно заменить "INNER JOIN" на "LEFT JOIN" больше ничего не меняя в запросе.

===================================================================================

Есть еще одно, довольно экзотическое, решение. Оно заведомо не оптимизируемо, поэтому выполняется достаточно медленно.

Способ решения отличается для версии Visual FoxPro 9 и младших версий, поскольку в 9 версии значительно расширены возможности команды Select-SQL


* Для версии младше Visual FoxPro 9

SELECT tabMain.*, tabChild.* ;
FROM tabMain ;
INNER JOIN tabChild ON tabMain.tabMainID=tabChild.tabMainID ;
WHERE tabChild.tabChildID IN ;
    (SELECT CTOBIN(RIGHT(MAX(DTOS(CurDate)+BINTOC(tabChildID)),4)) ;
            FROM tabChild GROUP BY tabMainID) ;
UNION ALL ;
SELECT tabMain.*, 0, 0, {}, '' ;
FROM tabMain ;
WHERE tabMain.tabMainID NOT IN (SELECT tabMainID FROM tabChild)

* Для Visual FoxPro 9

SELECT tabMain.*, tabChild.* ;
FROM tabMain ;
LEFT JOIN tabChild ON tabMain.tabMainID=tabChild.tabMainID ;
WHERE tabChild.tabChildID IS NULL ;
    OR tabChild.tabChildID IN ;
        (SELECT CTOBIN(RIGHT(MAX(DTOS(CurDate)+BINTOC(tabChildID)),4)) ;
            FROM tabChild GROUP BY tabMainID)

Основная идея заключается в том, что в подзапросе ищется не просто значение максимальной даты, а некоего "синтетического" ключа. Суммы строк даты и ключевого поля дочерней таблицы.

Для корректного определения максимального значения дата конвертируется в строку при помощи функции DTOS(). Т.е. это представления даты в виде "ГГГГММДД". Другими словами дата "15 июня 2005 года" будет выглядеть как "20050615".

В данном примере, ключевое поле дочерней таблицы имеет тип Integer. Для конвертации его в строку использована функция BINTOC() просто для того, чтобы получить как можно меньший размер. В данном случае получается строка длиной в 4 символа. А в случае стандартного преобразования через STR() пришлось бы выделить 10 символов. Отсечь ведущие пробелы в данном случае нельзя.

Найденное максимальное значение по такому выражению, по сути, означает, что в пределах одного значения поля tabMainID найдена запись с максимальным значением даты, а если существуют несколько записей с максимальным значением даты, то среди них отбирается запись с максимальным значением ключевого поля tabChilID.

После того, как будет найдено максимальное значение для такого "синтетического" ключа из него выделяются последние 4 символа и преобразуются к типу Integer. По сути, получаем значение кода записи дочерней таблицы для максимального значения даты.

Далее уже все просто. Используя конструкцию IN, отбираем нужные записи дочерней таблицы. Ну, а объединение нужно в том случае, если требуются еще данные из главной таблицы.

------------------

Исправлено 3 раз(а). Последнее : Владимир Максимов, 06.07.09 18:44

Тема	Просмотров	Написано	Написано
Работа с данными	6672	Владимир Максимов	01.03.05 22:04
Выбрать из дочерней таблицы записи с максимальной датой	22055	Владимир Максимов	15.06.05 21:08
Выбрать записи с повторяющимися (дублирующими) значениями поля	13046	Владимир Максимов	27.06.05 21:21
Как выполнить восстановление поврежденных индексов	10754	Владимир Максимов	29.06.05 21:04
Как изменить значения в одной таблице данными из другой	13380	Владимир Максимов	29.06.05 21:40
Запрос с GROUP BY выдает сообщение о синтаксической ошибке	12370	Владимир Максимов	20.07.05 17:47
При работе в сети иногда не открывается таблица	5954	Владимир Максимов	20.07.05 18:36
Как получить программный код создания структуры базы данных	8395	Владимир Максимов	18.09.05 11:14
Как восстановить поврежденную таблицу	21639	Владимир Максимов	31.08.08 23:02

:: Список тем • Новая Тема • Поиск • Войти

:: Список Форумов•Список тем•Поиск•Войти

Извините, но у вас нет прав для того, чтобы оставлять сообщения в этом форуме.

On-line: 1 (Гостей: 1)

	:: Главная :: Решения :: Статьи :: Проект "Русский help" :: Файловый архив :: Фотоальбом :: Ссылки ::
	Л и с о в о д ы в с е х с т р а н, о б ъ е д и н я й т е с ь !!!