Метка: SQL

Записи с меткой "SQL"

"Включаем" свойство галочка у товаров определённого производителя

Когда нужно включить галочку (проставить значение "1") у определённого свойства в товарах определённого производителя:

UPDATE `property_value_ints`
JOIN `shop_items` ON `property_value_ints`.`entity_id` = `shop_items`.`id`
   AND `shop_items`.`shop_producer_id` = 9
SET value = 1
WHERE `property_id` = 17

Присоединяем к таблице свойств типа чекбокс property_value_ints таблицу shop_items с отбором по значению id и по производителю с id=9. Назначаем свойству значение "1", где id свойства равно "13"

SQL

SQL запросы для YML файла (Яндекс.Маркет)

Отключить экспорт всех модификаций товаров в Я.Маркет

UPDATE `shop_items` SET `yandex_market` = 0 WHERE `modification_id` > 0;

Включить экспорт всем товарам в Яндекс.Маркет

UPDATE `shop_items` SET `yandex_market` = 1 WHERE `shop_id` = 1;

Отключение экспорта всем товарам в Яндекс.Маркет

UPDATE `shop_items` SET `yandex_market` = 0 WHERE `shop_id` = 1;

`count` = 0;

Отключение экспорта товаров в Я.Маркет определенной группы

UPDATE `shop_items` SET `yandex_market` = 0 WHERE `shop_group_id` = 717;

Снять всем товарам магазина галочку "Самовывоз" для экспорта в Яндекс.Маркет

UPDATE `shop_items` SET `pickup` = 0 WHERE `shop_id` = 123;

Снять галочку с чек-боксов "Доставка" и "Самовывоз"

UPDATE `shop_items` SET `delivery` = 0, `pickup` = 0;

UPDATE `shop_items` SET `yandex_market` = 0 WHERE `indexing` = 0;

SQL, Яндек.Маркет

SQL запросы для дополнительных свойств

Перенос значений свойства из property_value_strings в property_value_ints

INSERT INTO `property_value_ints` (`property_id`, `entity_id`, `value`) SELECT `property_id`, `entity_id`, `value` FROM `property_value_strings` WHERE `property_id` = 5

Добавляем свойство (в частности свойство флажок с id 13 и значением 1) для всех товаров определённого производителя.
Если магазинов в системе несколько, то указать ID маазина

INSERT INTO `property_value_ints`
(`property_id`, `entity_id`, `value`)
SELECT 13, `id`, 1 FROM `shop_items` WHERE `shop_producer_id` = 9;  // WHERE `shop_id` = 1 - если нужно, указать ID магазина, заменить 9 на ID нужного производителя

Установить значение "1 " для свойства с ID 555

UPDATE `property_value_ints` SET value = 1 WHERE `property_id` = 555

Удалить все значения свойства (действие отменить будет нельзя!):

- Числовое свойство:

DELETE FROM `property_value_ints` WHERE `property_id` = 123;

- Свойство с плавающей точкой:

DELETE FROM `property_value_floats` WHERE `property_id` = 123;

- Строковое свойство:

DELETE FROM `property_value_strings` WHERE `property_id` = 123;

- Текстовое свойство:

DELETE FROM `property_value_texts` WHERE `property_id` = 123;

- Дата-время:

DELETE FROM `property_value_datetimes` WHERE `property_id` = 123;

Удалить все свойства тип "Строка" с ID = 999 у всех товаров

DELETE FROM property_value_strings WHERE property_id = 999;

Удалить все свойства тип "Строчка" с ID = 998 и ID = 999 у всех товаров

DELETE FROM property_value_strings WHERE property_id IN (998,999)

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

OPTIMIZE TABLE property_value_strings;

Какие типы свойств к какой таблице относятся:

property_value_datetimes - "Дата [type=8]", "Дата-время [type=9]"
property_value_files - "Файл [type=2]"
property_value_floats - "Число с плавающей запятой [type=11]"
property_value_ints - "Список[type=3]", "Информационная система [type=5]", "Флажок [type=7]", "Интернет-магазин [type=12]"
property_value_strings - "Строка [type=1]", "Скрытое поле [type=10]"
property_value_texts - "Большое текстовое поле [type=4]", "Визуальный редактор [type=6]"

SQL, Дополнительные свойства

SQL запросы для модификаций товаров

Запрос на отключение модификаций в поиске

UPDATE `shop_items` SET `shop_items`.`indexing` = 0 WHERE `shop_items`.`modification_id` > 0 AND `shop_items`.`shop_id` = 1

Исключить модификации из результатов поиска. Cнятие индексирования со всех модификаций

Для исключения модификаций из результатов поиска выключите флаг "Индексировать" для всех модификаций и выполните переиндексацию сайта.

Отключить индексацию всех модификаций можно SQL-запросом:

UPDATE `shop_items` SET `indexing` = 0 WHERE `modification_id` > 0;

UPDATE `shop_items` SET `active` = 0 WHERE `modification_id` > 0;

после запроса переиндексировать страницы

Удаление меток у всех модификаций в магазине

DELETE tag_shop_items.* FROM tag_shop_items LEFT JOIN shop_items ON tag_shop_items.shop_item_id = shop_items.id WHERE shop_items.modification_id != "0" AND shop_items.shop_id ="3"

Удаление всех модификаций (товаров, которые модификации для других товаров)

UPDATE `shop_items` SET `deleted` = 1 WHERE `modification_id` > 0;

Удаление всех модификаций в самой базе данных

DELETE FROM `shop_items` WHERE `modification_id` > 0;

DELETE FROM `shop_items` WHERE `shop_group_id` = 0

SQL, Модификации товара

SQL запросы для товаров

НАЗНАЧАЕМ ВАЛЮТУ ВСЕМ ТОВАРАМ

UPDATE `polyankake_hs`.`shop_items` SET `shop_currency_id` = '1' WHERE `shop_id` = 123; // вместо 123 указать ID магазина

Меняем тип товара (делимый - 2, электронный - 1, обычный - 0)

UPDATE `shop_items` SET `type` = '2'

Удалить товары

// Удаляем товары, которых нет на всех складах
UPDATE `shop_items` SET `deleted` = (
SELECT IF(SUM(`shop_warehouse_items`.`count`) = 0, 0, 0)
FROM `shop_warehouse_items` WHERE `shop_warehouse_items`.`shop_item_id` = `shop_items`.`id`
)
WHERE `shop_items`.`shop_id` = 1;  // номер магазина заменить

// Удаляем все товары из указанного магазина
UPDATE `shop_items` SET `deleted` = 1 WHERE `shop_id` = 123 // вместо 123 указать ID магазина

// Удаляем товары у которых отключена активность
UPDATE `shop_items` SET `deleted` = 1 WHERE `shop_items`.`active` = 0

UPDATE `shop_items` SET `deleted` = 1 WHERE `deleted` = 1

// Удаляем товары у которых отключена индексация
UPDATE `shop_items` SET `deleted` = 1 WHERE `shop_items`.`indexing` = 0

Убрать все сопутствующие для всех товаров

TRUNCATE TABLE `shop_item_associated`;

Установить всем товарам на складе 55 остаток в единицу

UPDATE `shop_warehouse_items` SET `count` = 1 WHERE `shop_warehouse_id` = 55;

Назначаем всем товарам количество на основном складе

UPDATE `shop_warehouse_items` SET `count` = '100'

Назначаем количество только товарам с количеством = 0

UPDATE `shop_warehouse_items` SET `count` = '999' WHERE `count` = 0;

Установить "индексировать" товарам с наличием на складе и не индексировать с нулевым остатком

UPDATE `shop_items` SET `shop_items`.`indexing` = (

SELECT IF(SUM(`shop_warehouse_items`.`count`) > 0, 1, 0)

FROM `shop_warehouse_items` WHERE `shop_warehouse_items`.`shop_item_id` = `shop_items`.`id`

)
WHERE `shop_items`.`shop_id` = 123; // вместо 123 указать ID магазина

UPDATE `shop_items` SET `shop_items`.`indexing` = 0 WHERE `shop_items`.`active` = 0

Удалить артикул у всех товаров с Артикулом

UPDATE `shop_items` SET `marking` = '' WHERE `marking` != '' AND `shop_id` = 123; // вместо 123 указать ID магазина

Добавить к названию товара какое-то слово (символ) в определённой группе

update shop_items set name = concat('Пицца ', name) where shop_group_id = 746

Добавить к названию товара какое-то слово (символ) в нескольких группах

update shop_items set name = concat('Пицца ', name) where shop_group_id IN (746, 747, 748)

Добавить к названию товара артикул в определённых группах

update shop_items set name = concat(name, ' ', marking) where shop_group_id IN (746, 747, 748)

Удалить определённый символ из названия товара

UPDATE `shop_items` SET name = REPLACE(`name`,'*','х' ) // заменяем "*" на "х"

Отключить активность всем Купонам

UPDATE `shop_purchase_discount_coupons` SET `active` = 0

Удаление значений несуществующих товаров

DELETE `property_value_strings`
FROM `property_value_strings`
LEFT JOIN `shop_items` ON `property_value_strings`.`entity_id` = `shop_items`.`id`
WHERE `shop_items`.`id` IS NULL

DELETE `shop_warehouse_items`
FROM `shop_warehouse_items`
LEFT JOIN `shop_items` ON `shop_warehouse_items`.`shop_item_id` = `shop_items`.`id` 
WHERE `shop_items`.`id` IS NULL

SELECT `shop_item_prices`.`id`, `shop_items`.`id`
FROM `shop_item_prices`
LEFT JOIN `shop_items` ON `shop_item_prices`.`shop_item_id` = `shop_items`.`id`
WHERE `shop_items`.`id` IS NULL

Выбор товаров из группы и подгруппы

SELECT `shop_items`.* 
FROM `shop_items`
JOIN `shop_groups` ON `shop_groups``.id` = `shop_items`.`shop_group_id`
	AND (`shop_groups`.`parent_id` IN (1,2) OR `shop_groups`.`id` IN (1,2))
WHERE `shop_items`.`shortcut_id` = 0 AND `shop_items`.`modification_id` = 0

Поиск дублей в MySQL

SELECT COUNT(`path`) AS `count`, `id`, `shop_group_id`, `shop_producer_id`, `name`, `active`, `path`, `deleted` FROM `shop_items` WHERE `shop_id` = 1 AND `modification_id` = 0 AND `shortcut_id` = 0 GROUP BY `path` HAVING COUNT(`path`) > 1

Установить значение "1 " для свойства с ID 555

UPDATE `property_value_ints` SET value = 1 WHERE `property_id` = 555

Удалить SEO заголовки title, description, keywords у товаров определенной группы

UPDATE `shop_items` SET `seo_title` = '', `seo_description` = '',`seo_keywords`  = '' WHERE `shop_group_id` = 123

Удаление значений свойств у товаров

Удалить все свойства тип "Строка" с ID = 999 у всех товаров

DELETE FROM property_value_strings WHERE property_id = 999;

Удалить все свойства тип "Строчка" с ID = 998 и ID = 999 у всех товаров

DELETE FROM property_value_strings WHERE property_id IN (998,999)

OPTIMIZE TABLE property_value_strings;

Какие типы свойств к какой таблице относятся:

ДОСКА ОБЪЯВЛЕНИЙ

Обнуляем дату окончания показа объявлений

UPDATE `shop_items` SET `end_datetime` = '0000-00-00 00:00:00' WHERE `shop_id` = 123; // вместо 123 указать ID магазина

SQL, Товар

SQL запросы заказов ИМ

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

UPDATE `shop_orders` SET `unloaded` = 0 WHERE `shop_id` = 1;

Увеличить номер заказа в магазине до 1000-го

ALTER TABLE `shop_orders` AUTO_INCREMENT =1000;

SQL, Интернет-магазин

SQL запросы. Как быстро посчитать кол-во товаров

Заходим в модуль "SQL-запросы" и делаем такой запрос:

SELECT COUNT(`id`) AS `count` 
FROM `shop_items` 
WHERE `shop_id` = 1 AND `active` = 1 AND `deleted` = 0

Или так, усложняем, проверяем еще активность и удалена ли группа товара:

SELECT COUNT(`shop_items`.`id`) AS `count` 
FROM `shop_items`
LEFT JOIN `shop_groups` ON `shop_items`.`shop_group_id` = `shop_groups`.`id`
WHERE `shop_items`.`shop_id` = 1 AND `shop_items`.`active` = 1 AND `shop_items`.`deleted` = 0 AND `shop_groups`.`active` = 1 AND `shop_groups`.`deleted` = 0

Для чего мы делаем условия, да для того чтобы посчитать именно в нужном магазине (`shop_id` = 1), т.к. в системе могут быть несколько магазинов или остались старые товары от старого магазина, мы считаем только активные товары (`active` = 1) и не удаленные (`deleted` = 0), после чего будет видно кол-во

Если у Вас в магазине присутствуют модификации и нужно посчитать без них, то исключаем их:

SELECT COUNT(`id`) AS `count`
FROM `shop_items`
WHERE `shop_id` = 1 AND `modification_id` > 0 AND `active` = 1 AND `deleted` = 0

Считаем кол-во товаров у которых есть картинки:

ELECT COUNT(`id`) AS `count`
FROM `shop_items`
WHERE `shop_id` = 1 AND `image_small` != '' AND `active` = 1 AND `deleted` = 0

Считаем кол-во товаров у которых есть цены:

SELECT COUNT(`id`) AS `count`
FROM `shop_items`
WHERE `shop_id` = 1 AND `price` > 0 AND `active` = 1 AND `deleted` = 0

Считаем кол-во товаров у которых нет артикула:

SELECT COUNT(`id`) AS `count`
FROM `shop_items`
WHERE `shop_id` = 1 AND `marking` = '' AND `active` = 1 AND `deleted` = 0

SQL, Интернет-магазин, Товар

SQL запросы. Ошибки в базе данных

Если при работе с базой данных через PhpMyadmin возникает ошибка "Данная таблица не содержит уникального столбца. Изменение сетки, выставление галочки, редактирование, копирование и удаление не доступно", нужно выполнить SQL запрос в базе данных

ALTER TABLE tablename ADD newid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Только вместо tablename необходимо поставить название проблемной таблицы (без скобок и кавычек).

SQL, Ошибки

SQL запросы. Разное

Удалить все title, description, keywords

UPDATE `shop_groups` SET `description` = '', `seo_title` = '', `seo_description` = '', `seo_keywords` = '' WHERE `shop_id` = 1

Меняем path (URL) у всех элементов, делая его равным id

UPDATE `shop_items` SET `path` = `id`; - для товаров

UPDATE `informationsystem_items ` SET `path` = `id`; - для инфоэлементов

Использование HTTPS при работе с клиентским разделом

Установите SSL сертификат на сервере, далее в структуре сайта для всех разделов выберите опцию "Доступ через HTTPS". Ускорить установку всем разделам этой опции можно через SQL-запрос (123 замените на идентификатор сайта):

UPDATE `structures` SET `https` = 1 WHERE `site_id` = 123;

SQL

Исправление ошибки кодировки таблиц

В результате проверки системы, Битрикс указал, что есть ошибки в Базе данных, но иногда автоматически он не может всё исправить, и тут приходится исправлять своими ручками.

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

Подробное описание ошибки

Если перейдем по ссылке на "журнал проверки системы", то увидим список тех таблиц, где не совпали кодировки, вот нам они и нужны, на скриншоте пометил стрелочками.

Подробное описание ошибки

SQL запрос:

ALTER TABLE b_iblock_property_feature CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Где b_iblock_property_feature - это название таблицы, у Вас могут быть другие.

Таким образом я выписал и составил список sql запросов для всех своих таблиц из журнала.

ALTER TABLE b_iblock_property_feature CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_block CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_demo CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_domain CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_file CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_hook_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_manifest CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_placement CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_repo CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_site CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_syspage CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_template CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_landing_template_ref CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_main_mail_blacklist CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_main_mail_sender CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_messageservice_message CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_messageservice_rest_app CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_messageservice_rest_app_lang CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_mobileapp_app CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_mobileapp_config CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_numerator CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_numerator_sequence CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rating_voting_reaction CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_ap CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_ap_permission CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_app CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_app_lang CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_app_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_event CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_event_offline CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_placement CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_stat CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_rest_stat_method CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_seo_service_subscription CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_user_profile_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_user_profile_record CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_utm_iblock_6_section CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_utm_iblock_8_section CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_uts_iblock_6_section CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE b_uts_iblock_8_section CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

После того как Вы собрали список SQL запросов, заходим в админку сайта битрикс (Настройки - Инструменты - SQL-запрос) или по пути, вставляете после адреса сайта /bitrix/admin/sql.php

SQL, Ошибки, php, Настройки

Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER

Разберем пример. Имеем две таблицы: пользователи и отделы.

U) users D) departments

id name d_id id name

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

1 Владимир 1 1 Сейлз

2 Антон 2 2 Поддержка

3 Александр 6 3 Финансы

4 Борис 2 4 Логистика

5 Юрий 4

SELECT u.id, u.name, d.name AS d_name
FROM users u
INNER JOIN departments d ON u.d_id = d.id

Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN ON <..>.

В нашем случае условие <таблица_пользователей>.<идентификатор_отдела> должен совпадать с <таблица_отделов>.<идентификатор>

В результате отсутствуют:

- пользователь Александр (отдел 6 - не существует)

- отдел Финансы (нет пользователей)

id name d_name

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

1 Владимир Сейлз

2 Антон Поддержка

4 Борис Поддержка

3 Юрий Логистика

sql inner join

Внутреннее объединение INNER JOIN (синоним JOIN, ключевое слово INNER можно опустить).

Выбираются только совпадающие данные из объединяемых таблиц.

Чтобы получить данные, которые подходят по условию частично, необходимо использовать внешнее объединение - OUTER JOIN.

Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.

sql left join

Существует два типа внешнего объединения OUTER JOIN - LEFT OUTER JOIN и RIGHT OUTER JOIN.

Работают они одинаково, разница заключается в том что LEFT - указывает что "внешней" таблицей будет находящаяся слева (в нашем примере это таблица users).

Ключевое слово OUTER можно опустить. Запись LEFT JOIN идентична LEFT OUTER JOIN.

SELECT u.id, u.name, d.name AS d_name
FROM users u
LEFT OUTER JOIN departments d ON u.d_id = d.id

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

id name d_name

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

1 Владимир Сейлз

2 Антон Поддержка

3 Александр NULL

4 Борис Поддержка

5 Юрий Логистика

Добавив условие

WHERE d.id IS NULL

в выборке останется только 3#Александр, так как у него не назначен департамент.

sql left outer join

RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.

SELECT u.id, u.name, d.name AS d_name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id

id name d_name

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

1 Владимир Сейлз

2 Антон Поддержка

4 Борис Поддержка

NULL NULL Финансы

5 Юрий Логистика

Дополнительно можно отфильтровать данные, проверяя их на NULL.

SELECT d.id, d.name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id 
WHERE u.id IS null

В нашем примере указав WHERE u.id IS null, мы выберем департаменты, в которых не числятся пользователи. (3#Финансы)

Cross/Full Join

FULL JOIN возвращает `объединение` объединений LEFT и RIGHT таблиц, комбинируя результат двух запросов.

CROSS JOIN возвращает перекрестное (декартово) объединение двух таблиц. Результатом будет выборка всех записей первой таблицы объединенная с каждой строкой второй таблицы. Важным моментом является то, что для кросса не нужно указывать условие объединения.

Дублирование строк при использовании JOIN

При использовании объединения новички часто забывают что результирующая выборка может содержать дублирующиеся данные!

Если вам нужна одна запись, делайте объединение с подзапросом

SELECT t1.*, t2.* from left_table t1 left join (select * from right_table where some_column = 1 limit 1) t2 ON t1.id =t2.join_id

Self Join

Выборка из одной и той же таблицы для нескольких условий.

Рассмотрим задачку от яндекса:

Есть таблица товаров.

CREATE TABLE `ya_goods` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into ya_goods values (1, 'яблоки'), (2, 'яблоки') ,(3, 'груши'), (4,'яблоки'), (5, 'апельсины'), (6, 'груши');

Она содержит следующие значения.

`id` `name`

1 Яблоки

2 Яблоки

3 Груши

4 Яблоки

5 Апельсины

6 Груши

Напишите запрос, выбирающий уникальные пары `id` товаров с одинаковыми `name`, например:

(1,2), (4,1), (2,4), (6,3)...

При решении задачи необходимо учесть, что пары (x,y) и (y,x) — одинаковы.

Решение:

SELECT g1.id id1, g2.id id2
-- CONCAT('(', LEAST(g1.id, g2.id), ',', GREATEST(g1.id, g2.id), ')') row
FROM ya_goods g1 
INNER JOIN ya_goods g2 ON g1.name = g2.name 
WHERE g1.id <> g2.id
GROUP BY LEAST(g1.id, g2.id), GREATEST(g1.id, g2.id)
ORDER BY g1.id;
 
-- или без группировки (быстрее)
 
SELECT DISTINCT CONCAT('(', LEAST(g1.id, g2.id), ',', GREATEST(g1.id, g2.id), ')') row
FROM ya_goods g1 
INNER JOIN ya_goods g2 ON g1.name = g2.name 
WHERE g1.id <> g2.id

Объединяем таблицы ya_goods по одинаковому полю `name`, группируем по уникальным idентификаторам и получаем результат.

(1,2)(1,4)(2,4)(3,6)

Множественное объединение multi join

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

Пример: набор вариантов (вес, объем) товаров.

Продукты в таблице products, Варианты - таблица product_options, Значения вариантов - таблица product2options

Необходимо: фильтровать продукты по дате, и имеющимся вариантам

CREATE TABLE  `products` (
  `id` int(11),
  `title` varchar(255),
  `created_at` datetime
)
 
CREATE TABLE `product_options` (
  `id` int(11),
  `name` varchar(255)
)
 
CREATE TABLE `product2options` (
  `product_id` int(11),
  `option_id` int(11),
  `value` int(11)
)

Тестовые данные

INSERT INTO `products` (`id`, `title`, `created_at`) VALUES
        (1, 'Кружка', '2009-01-17 20:00:00'),
        (2, 'Ложка', '2009-01-18 20:00:00'),
        (3, 'Тарелка', '2009-01-19 20:00:00');
 
INSERT INTO `product_options` (`id`, `name`) VALUES
        (11, 'Вес'),
        (12, 'Объем');
 
INSERT INTO `product2options` (`product_id`, `option_id`, `value`) VALUES
        (1, 11, 200),
        (1, 12, 250),
        (2, 11, 35),
        (2, 12, 15),
        (3, 11, 310),
        (3, 12, 300),
        (2, 11, 45),
        (2, 12, 25);

Пример: выбрать товары,

добавленные после 17/01/2009 в следующих вариантах:

- вес=310, объем=300

- вес=35, объем=15

- вес=45, объем=25

- вес=200, объем=250

Просто перечислить условия вариантов в подзапросе/джоине через OR/AND не сработает,

необходимо осуществить объединение таблиц вариантов равное количеству этих самых вариантов (у нас - 2: объем и вес)

SELECT p.*, po1.name 'P1', p2o1.value, po2.name 'P2', p2o2.value     
 
FROM products p       
 
INNER JOIN product2options p2o1 ON p.id = p2o1.product_id
INNER JOIN product_options po1  ON po1.id = p2o1.option_id
 
INNER JOIN product2options p2o2 ON p.id = p2o2.product_id
INNER JOIN product_options po2  ON po2.id = p2o2.option_id
 
WHERE p.created_at > '2009-01-17 21:00' 
  AND ( -- тарелка#3 
  p2o1.option_id = 11 AND p2o1.value = 310 
  AND p2o2.option_id = 12 AND p2o2.value = 300
  OR  -- ложка#2
  p2o1.option_id = 11 AND p2o1.value = 35 
  AND p2o2.option_id = 12 AND p2o2.value = 15           
  OR  -- ложка#2
  p2o1.option_id = 11 AND p2o1.value = 45 
  AND p2o2.option_id = 12 AND p2o2.value = 25   
  OR  -- кружка#1 не попадает по дате
  p2o1.option_id = 12 AND p2o1.value = 250 
  AND p2o2.option_id = 11 AND p2o2.value = 200
  );

Результ выборки:

id title created_at P1 value P2 value

2 Ложка 2009-01-18 20:00:00 Вес 35 Объем 15

3 Тарелка 2009-01-19 20:00:00 Вес 310 Объем 300

2 Ложка 2009-01-18 20:00:00 Вес 45 Объем 25

-- не попадает по дате

1 Кружка 2009-01-17 20:00:00 Объем 250 Вес 200

UPDATE и JOIN

Объединение можно использовать совместно с UPDATE.

Например, имеем таблицу houses (id, title, area). Нужно выбрать title, если в нем встречается `число м2`, заменить поле area, если оно меньше. Т.к. в mysql отстутсутствует поддержка регулярных выражений, нужно немного поколдовать с locate и substr.

В подзапросе выбираем интересующие нас данные, и в финальной стадии осуществляем обновление данных подходящий по критерию (p5 > area).

UPDATE houses base
INNER JOIN (
-- Антарис аренда офиса 1594 м2, по ставке 12700 руб. м2/год -> 1594
SELECT
        id,
        @baseString := title title,
        @areaTitleEnd := LOCATE(' м2', @baseString) as p2,
        @tmpString := LTRIM(REVERSE(SUBSTR(@baseString, 1, @areaTitleEnd))) as p3,
        @areaTitleBegin := LEFT(@tmpString, -1 + LOCATE(' ', @tmpString)) as p4,
        @value := CAST(REVERSE(@areaTitleBegin) as UNSIGNED) as p5
 
        FROM ga_pageviews
        WHERE title like '%м2%'
) calc USING (`id`)
SET base.area = calc.p5
WHERE base.area < calc.p5

DELETE и JOIN

Рассмотрим пример с удалением дубликатов. Есть таблица tableWithDups (id, email). Нужно удалить строки с одинаковыми email:

DELETE tableWithDups
FROM tableWithDups
INNER JOIN (
        SELECT MAX(id) AS lastId, email
        FROM tableWithDups
        GROUP BY email
        HAVING COUNT(*) > 1
        ) dups ON dups.email = tableWithDups.email
WHERE tableWithDups.id < dups.lastId;

Последние два примера не совместимы с ANSI SQL, но работают в mySQL.

SQL