FULL OUTER JOIN 1. В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц. 2. В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL. 3. В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL.
ВПР или ИНДЕКС реализуют LEFT OUTER JOIN Если фильтром скрыть строки с #Н/Д, то получится INNER JOIN А как реализовать FULL OUTER JOIN?
Пример в прикрепленном файле.
FULL OUTER JOIN 1. В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц. 2. В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL. 3. В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL.
ВПР или ИНДЕКС реализуют LEFT OUTER JOIN Если фильтром скрыть строки с #Н/Д, то получится INNER JOIN А как реализовать FULL OUTER JOIN?
подойдет, но я наверно плохо объяснил. Изначально на листах есть только три столбца: id_1, id_2, "начислено" на одном листе и id_1, id_2, "уплачено" на втором. Остальные столбцы добавлены уже мной в процессе попытки решить задачу. Сходу не разобрался как создана сводная таблица, но результаты там задвоены. Берем первую строку со значениями на 1 листе (1 a 10) и (1 а) ищем на втором листе, такая строка есть, значит на 3 лист добавляем всю строку с первого листа (1 a 10) и в следующий столбец заносим значение из соответствующей строки 2 листа - это 15 и на 3 листе получим (1 a 10 15). Еще как то надо пометить строку на 2 листе как использованную. В SQL это внутреннее соединение. Берем следующую строку на 1 листе (1 b) и выполнили бы те же действия, если бы (1 b) нашли во второй таблице. Но поскольку (1 b) там нет, то на 3 лист в новую строку заносим значения с 1 листа (1 b 25), а 4-й столбец не заполняем. В SQL это левостороннее соединение. И так обрабатываем все строки с 1 листа. После этого пробегаемся по неиспользованным строкам на 2-м листе и заносим их на 3 лист, но значение из 1 и 2 столбца заносим в 1 и 2 столбец 3-го листа, а значение 3 столбца из 2-го листа заносим в 4 столбец 3-го листа (чтобы уплата не попала в начисление). Значение 3 столбца оставляем пустым. Если снова не понятно излагаю, может еще раз прочитать определение FULL OUTER JOIN в СТ PS Может быть там где написано "оставляем пустым" заменить на "заносим нуль", чтобы позже не было проблем с выполнением арифметических операций.
подойдет, но я наверно плохо объяснил. Изначально на листах есть только три столбца: id_1, id_2, "начислено" на одном листе и id_1, id_2, "уплачено" на втором. Остальные столбцы добавлены уже мной в процессе попытки решить задачу. Сходу не разобрался как создана сводная таблица, но результаты там задвоены. Берем первую строку со значениями на 1 листе (1 a 10) и (1 а) ищем на втором листе, такая строка есть, значит на 3 лист добавляем всю строку с первого листа (1 a 10) и в следующий столбец заносим значение из соответствующей строки 2 листа - это 15 и на 3 листе получим (1 a 10 15). Еще как то надо пометить строку на 2 листе как использованную. В SQL это внутреннее соединение. Берем следующую строку на 1 листе (1 b) и выполнили бы те же действия, если бы (1 b) нашли во второй таблице. Но поскольку (1 b) там нет, то на 3 лист в новую строку заносим значения с 1 листа (1 b 25), а 4-й столбец не заполняем. В SQL это левостороннее соединение. И так обрабатываем все строки с 1 листа. После этого пробегаемся по неиспользованным строкам на 2-м листе и заносим их на 3 лист, но значение из 1 и 2 столбца заносим в 1 и 2 столбец 3-го листа, а значение 3 столбца из 2-го листа заносим в 4 столбец 3-го листа (чтобы уплата не попала в начисление). Значение 3 столбца оставляем пустым. Если снова не понятно излагаю, может еще раз прочитать определение FULL OUTER JOIN в СТ PS Может быть там где написано "оставляем пустым" заменить на "заносим нуль", чтобы позже не было проблем с выполнением арифметических операций.SergeyKorotun
Сообщение отредактировал SergeyKorotun - Понедельник, 18.11.2013, 00:42
А я всё же считаю, что проще в данном случае сделать через ADO. Поскольку текст запроса можно сначала программно собрать в текстовую переменную, то количество ключевых полей значения не имеет. Пример реализации FULL OUTER JOIN - см. в файле. Единственное - если надо получить записи в результате именно в том виде, что вы хотите (сначала INNER JOIN, затем OUTER), то можно добавить в запросы поле для сортировки, которое затем исключить в объединенной выборке...
А я всё же считаю, что проще в данном случае сделать через ADO. Поскольку текст запроса можно сначала программно собрать в текстовую переменную, то количество ключевых полей значения не имеет. Пример реализации FULL OUTER JOIN - см. в файле. Единственное - если надо получить записи в результате именно в том виде, что вы хотите (сначала INNER JOIN, затем OUTER), то можно добавить в запросы поле для сортировки, которое затем исключить в объединенной выборке...AndreTM
Select Distinct * from ((select * from t1 left join t2 on t1.id=t2.id ...) union (select * from t1 right join t2 on t1.id=t2.id ...))
[/vba] не всегда есть аналогом FULL OUTER JOIN Например, если в [vba]
Код
(t1 left join t2 on t1.id=t2.id ...)
[/vba] условия t1.id=t2.id ... не будут обеспечивать связь один к одному, то Distinct удалит не только одну из задвоенных записей, включенные в выборку и по left join и по right join, но и удалит нужные записи, полученные в результате соединения один ко многим.
Select Distinct * from ((select * from t1 left join t2 on t1.id=t2.id ...) union (select * from t1 right join t2 on t1.id=t2.id ...))
[/vba] не всегда есть аналогом FULL OUTER JOIN Например, если в [vba]
Код
(t1 left join t2 on t1.id=t2.id ...)
[/vba] условия t1.id=t2.id ... не будут обеспечивать связь один к одному, то Distinct удалит не только одну из задвоенных записей, включенные в выборку и по left join и по right join, но и удалит нужные записи, полученные в результате соединения один ко многим.SergeyKorotun
Сообщение отредактировал SergeyKorotun - Пятница, 22.11.2013, 13:24
условия t1.id=t2.id ... не будут обеспечивать связь один к одному
А вот это и есть та самая тонкость реализации. Ибо, с точки зрения сиквела, - мы предполагаем связь именно по уникальному ключевому выражению... Я не зря тонко намекнул, что текст запроса мы собираем программно - поэтому имеем возможность собрать именно уникальный ключ. А для связей типа "один-ко-многим" - достаточно сначала сформировать ключ (скажем, номер записи/строки), и использовать его в объединённом запросе...
условия t1.id=t2.id ... не будут обеспечивать связь один к одному
А вот это и есть та самая тонкость реализации. Ибо, с точки зрения сиквела, - мы предполагаем связь именно по уникальному ключевому выражению... Я не зря тонко намекнул, что текст запроса мы собираем программно - поэтому имеем возможность собрать именно уникальный ключ. А для связей типа "один-ко-многим" - достаточно сначала сформировать ключ (скажем, номер записи/строки), и использовать его в объединённом запросе...AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Пятница, 22.11.2013, 18:34
Не могу продублировать вашу консолидирующую таблицу. У вас в настройках чекер стоит только возле "подписи верхней строки". Мне, чтобы добиться аналогичного результата, пришлось установить еще два чекера ("значения левого столбца" и "создавать связи с исходными данными"). Но тогда появляются скрытые строки с данными, из которых собраны консолидирующие строки. Смотрите "Лист5".
Не могу продублировать вашу консолидирующую таблицу. У вас в настройках чекер стоит только возле "подписи верхней строки". Мне, чтобы добиться аналогичного результата, пришлось установить еще два чекера ("значения левого столбца" и "создавать связи с исходными данными"). Но тогда появляются скрытые строки с данными, из которых собраны консолидирующие строки. Смотрите "Лист5".SergeyKorotun
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
