Задать вопрос
grigor007
@grigor007
http://goldapp.ru
mysql

Mysql GROUP_CONCAT DISTINCT — единственный способ избавиться от дублей?

Помогите советом!

Я делаю несколько LEFT JOIN. Использую GROUP_CONCAT DISTINCT. Но это не совсем правильно. Хотелось бы, чтобы таблицы присоединялись один раз по id, а если текстовые значения одинаковые - то пусть остаются. Может нужны правильные последовательности LEFT JOIN или нужно использовать HAVING или USING?

CREATE TABLE `events` (
  `id` int(11) NOT NULL auto_increment,
  `e_name` text collate utf8_unicode_ci NOT NULL,
  `e_type` int(11) NOT NULL,
  `e_s_description` text collate utf8_unicode_ci NOT NULL,
  `e_f_description` text collate utf8_unicode_ci NOT NULL,
  `e_link` text collate utf8_unicode_ci,
  `e_add_by_user` int(11) NOT NULL,
  `e_visibility` enum('0','1') collate utf8_unicode_ci NOT NULL default '0',
  `e_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `e_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

INSERT INTO `events` VALUES (4, 'Advances in Optimization and Statistics', 1, '', '', '', 1, '1', '1', '2014-11-13 22:44:47');

CREATE TABLE `events_dates` (
  `id` int(11) NOT NULL auto_increment,
  `ed_event_id` int(11) NOT NULL,
  `ed_date_start` date NOT NULL,
  `ed_place_start` text collate utf8_unicode_ci NOT NULL,
  `ed_schedule` text collate utf8_unicode_ci NOT NULL,
  `ed_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `ed_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ed_event_id` (`ed_event_id`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=29 ;

INSERT INTO `events_dates` VALUES (27, 4, '2014-05-15', 'Institute of Information Transmission Problems of RAS (Kharkevich Institute)', '<p style="text-align:start">10:00 &ndash; 10:35<br />\r\n<strong>Asymptotics beats Monte Carlo: The case of correlated local vol baskets</strong></p>\r\n', '1', '2014-11-13 22:44:47');
INSERT INTO `events_dates` VALUES (28, 4, '2014-05-16', 'Institute of Information Transmission Problems of RAS (Kharkevich Institute)', '<p style="text-align:start">10:00 &ndash; 10:35<br />\r\n<strong></p>\r\n', '1', '2014-11-13 22:44:47');

CREATE TABLE `events_projects` (
  `id` int(11) NOT NULL auto_increment,
  `ep_event_id` int(11) NOT NULL,
  `ep_proj_id` int(11) NOT NULL,
  `ep_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `ep_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `ep_event_id` (`ep_event_id`,`ep_proj_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

INSERT INTO `events_projects` VALUES (1, 1, 2, '1', '2014-11-03 14:14:44');
INSERT INTO `events_projects` VALUES (2, 1, 3, '1', '2014-11-03 14:14:44');
INSERT INTO `events_projects` VALUES (3, 2, 3, '1', '2014-11-03 17:28:37');
INSERT INTO `events_projects` VALUES (4, 3, 2, '1', '2014-11-07 15:58:21');
INSERT INTO `events_projects` VALUES (5, 4, 9, '1', '2014-11-13 22:44:47');
INSERT INTO `events_projects` VALUES (6, 4, 8, '1', '2014-11-13 22:44:47');
INSERT INTO `events_projects` VALUES (7, 4, 7, '1', '2014-11-13 22:44:47');
INSERT INTO `events_projects` VALUES (8, 4, 6, '1', '2014-11-13 22:44:47');
INSERT INTO `events_projects` VALUES (9, 4, 5, '1', '2014-11-13 22:44:47');
INSERT INTO `events_projects` VALUES (10, 4, 4, '1', '2014-11-13 22:44:47');

CREATE TABLE `events_research_directions` (
  `id` int(11) NOT NULL auto_increment,
  `erd_event_id` int(11) NOT NULL,
  `erd_res_dir_id` int(11) NOT NULL,
  `erd_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `erd_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `erd_event_id` (`erd_event_id`,`erd_res_dir_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

INSERT INTO `events_research_directions` VALUES (1, 1, 2, '0', '2014-10-30 15:27:13');
INSERT INTO `events_research_directions` VALUES (2, 1, 1, '1', '2014-11-03 14:14:44');
INSERT INTO `events_research_directions` VALUES (3, 2, 1, '1', '2014-11-03 17:28:37');
INSERT INTO `events_research_directions` VALUES (4, 3, 1, '0', '2014-11-07 15:58:21');
INSERT INTO `events_research_directions` VALUES (5, 4, 9, '1', '2014-11-13 22:44:47');
INSERT INTO `events_research_directions` VALUES (6, 4, 8, '1', '2014-11-13 22:44:47');
INSERT INTO `events_research_directions` VALUES (7, 4, 7, '1', '2014-11-13 22:44:47');
INSERT INTO `events_research_directions` VALUES (8, 4, 6, '1', '2014-11-13 22:44:47');
INSERT INTO `events_research_directions` VALUES (9, 4, 5, '1', '2014-11-13 22:44:47');
INSERT INTO `events_research_directions` VALUES (10, 4, 4, '1', '2014-11-13 22:44:47');

CREATE TABLE `projects` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pr_name` text collate utf8_unicode_ci NOT NULL,
  `pr_s_description` text collate utf8_unicode_ci NOT NULL,
  `pr_f_description` text collate utf8_unicode_ci NOT NULL,
  `pr_customer` text collate utf8_unicode_ci NOT NULL,
  `pr_add_by_user` int(10) unsigned NOT NULL,
  `pr_visibility` enum('0','1') collate utf8_unicode_ci NOT NULL default '0',
  `pr_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `pr_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `projects` VALUES (3, 'проект 2', 'краткое проект 2', '<p>полное проект 2</p>', '', 1, '0', '0', '2014-10-31 18:53:13');
INSERT INTO `projects` VALUES (4, 'Financial engineering and energy management', 'The requirements for stochastic and numerical', '<p style="text-align:start">As opposed to purely financial obligations like stocks and bonds, energy (electricity, natural gas, oil etc.) can be physically stored. Storage allows for temporal transfer of the energy and permits exploitation of the fluctuating market energy prices. </p>', '', 1, '1', '1', '2014-11-13 17:27:23');
INSERT INTO `projects` VALUES (5, 'Digital imaging and signal processing', 'Description will be provided later.', '<p>Description will be provided later.</p>', '', 1, '1', '1', '2014-11-13 17:29:08');
INSERT INTO `projects` VALUES (6, 'Structural diagnostics in technical, economical, and natural processes', 'Description will be provided later.', '<p>Description will be provided later.</p>', '', 1, '1', '1', '2014-11-13 17:30:45');
INSERT INTO `projects` VALUES (7, 'Data mining', 'Description will be provided later.', '<p>Description will be provided later.</p>', '', 1, '1', '1', '2014-11-13 17:32:25');
INSERT INTO `projects` VALUES (8, 'Modeling and optimization of complex systems', 'Description will be provided later.', '<p>Description will be provided later.</p>', '', 1, '1', '1', '2014-11-13 17:34:03');
INSERT INTO `projects` VALUES (9, 'Equilibrium flows in congested traffic systems', 'Description will be provided later.', '<p>Description will be provided later.</p>', '', 1, '1', '1', '2014-11-13 17:34:50');

CREATE TABLE `research_direction` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `rd_s_description` text collate utf8_unicode_ci NOT NULL,
  `rd_f_description` text collate utf8_unicode_ci NOT NULL,
  `rd_name` text collate utf8_unicode_ci NOT NULL,
  `rd_add_by_user` int(11) NOT NULL,
  `rd_visibility` enum('0','1') collate utf8_unicode_ci NOT NULL,
  `rd_is_active` enum('0','1') collate utf8_unicode_ci NOT NULL default '1',
  `rd_date_add` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `research_direction` VALUES (1, 'краткое описание 1 ред', '<p>полно<em>е опписание 1 редактированное</em></p>', 'исследование 1 ред', 1, '1', '0', '2014-10-25 19:10:36');
INSERT INTO `research_direction` VALUES (2, 'краткое описание 2', '<p><u>полное описание 2</u><img alt="" src="/ck_upload_images/xQq27PQD.jpg" style="height:546px; width:481px" /></p>', 'исследование 2', 1, '0', '0', '2014-10-26 00:32:29');
INSERT INTO `research_direction` VALUES (4, 'The use of statistical methods for analyzing data', '<p style="text-align:start"><strong>Inverse problems</strong> are related to applications, where the quantities of interest cannot be measured directly. </p>', 'Statistical methods', 1, '1', '1', '2014-11-13 20:43:45');
INSERT INTO `research_direction` VALUES (5, 'Structural adaptive inference provide basis for most of data analysis algorithms developed at PreMoLab. This approach enables efficient analysis of complex statistical models.', '<p>Structural adaptive inference prov</p>', 'Structural adaptive inference', 1, '1', '1', '2014-11-13 20:45:20');
INSERT INTO `research_direction` VALUES (6, 'Application-specific and structure-specific optimization methods; optimization looking', '<p>Application-specific and structure-specific optimization methods; optimization looking &ldquo;inside the black box.&rdquo;</p>', 'Structural optimization', 1, '1', '1', '2014-11-13 20:46:58');
INSERT INTO `research_direction` VALUES (7, 'Description will be provided later.', '<p>Description will be provided later.</p>', 'Stochastic optimization and optimal stopping', 1, '1', '1', '2014-11-13 20:48:20');
INSERT INTO `research_direction` VALUES (8, 'Description will be provided later.', '<p>Description will be provided later.</p>', 'Huge-scale problems', 1, '1', '1', '2014-11-13 20:49:19');
INSERT INTO `research_direction` VALUES (9, 'Description will be provided later.', '<p>Description will be provided later.</p>', 'Primal-dual subgradient methods', 1, '1', '1', '2014-11-13 20:50:39');
  • Вопрос задан
  • 2868 просмотров
Комментировать
Подписаться 2 Оценить Комментировать
Решения вопроса 1
Finesse
@Finesse
А где сам запрос на выборку? Покажите запрос, подскажем, как улучшить.

Можно так (избавляемся от кучи JOINов и облегчаем жизнь СУБД):
SELECT
events.id, 
events.e_name,
events.e_s_description, 
events.e_type, 
events_type.et_name,
(SELECT GROUP_CONCAT(events_dates.id SEPARATOR '|') FROM events_dates WHERE events_dates.ed_event_id = events.id AND events_dates.ed_is_active = '1') AS ed_ids,
(SELECT GROUP_CONCAT(events_dates.ed_date_start SEPARATOR '|') FROM events_dates WHERE events_dates.ed_event_id = events.id AND events_dates.ed_is_active = '1') AS ed_date_start,
(SELECT GROUP_CONCAT(events_projects.ep_proj_id SEPARATOR '|') FROM events_projects WHERE events_projects.ep_event_id = events.id AND ep_is_active = '1') AS ep_proj_id,
(SELECT GROUP_CONCAT(projects.pr_name SEPARATOR '|') FROM projects WHERE projects.id IN (SELECT events_projects.ep_proj_id FROM events_projects WHERE events_projects.ep_event_id = events.id AND ep_is_active = '1')) AS pr_name,
(SELECT GROUP_CONCAT(events_research_directions.erd_res_dir_id SEPARATOR '|') FROM events_research_directions WHERE events_research_directions.erd_event_id = events.id AND erd_is_active = '1') AS erd_res_dir_id
(SELECT GROUP_CONCAT(research_direction.rd_name SEPARATOR '|' ) FROM research_direction WHERE research_direction.id IN (SELECT events_research_directions.erd_res_dir_id FROM events_research_directions WHERE events_research_directions.erd_event_id = events.id AND erd_is_active = '1')) AS rd_name
FROM events
LEFT OUTER JOIN events_type ON events_type.id = events.e_type
WHERE 
 events.e_is_active = '1' 
AND events.e_type = 1
 ORDER BY events.e_date_add DESC
Ответ написан
19 комментариев
19 комментариев
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Программист C++ Builder / базы данных MySQL
RU Electronics • Москва
от 180 000 до 200 000 ₽
Инженер технической поддержки с английским языком и знанием PHP/MySQL
IT-Aces
от 100 000 до 150 000 ₽
Второй ведущий разработчик (Fullstack, PHP, Laravel, Vue, Mysql)
Donatov.net
от 100 000 до 300 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Обновить C# приложения с net4 до net8. Платформа для трейдинга
25 апр. 2024, в 11:33
2500000 руб./за проект
Нужен человек с опытом работы с библиотекой SwiperJS
03 мая 2024, в 03:55
1000 руб./за проект
Скрипт для оценки ссылки через Virus Total
03 мая 2024, в 02:30
3000 руб./за проект
Ещё заказы