Создание сайтов

Реклама в интернете

Графический дизайн

Фирменный стиль

 



Статьи → MySQL → Построение дерева средствами MySQL без ограничений по количеству уровней

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

Создаем таблицу goods с полями:
  • id (код)
  • id_group (код верхней группы)
  • name (наименование)

Код для создания таблицы:

CREATE TABLE `goods` (
	id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`id_group` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
	`name` VARCHAR(50) COLLATE cp1251_general_ci NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	UNIQUE KEY `id` (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'cp1251' COLLATE 'cp1251_general_ci';

Заполняем таблицу:

INSERT goods values (0,0,'Велосипеды');
INSERT goods values (0,0,'Мопеды');
INSERT goods values (0,0,'Мотороллеры');
INSERT goods values (0,1,'«Школьник»');
INSERT goods values (0,2,'Мопед 2-х тактовый');
INSERT goods values (0,2,'Мопед 4-х тактовый');
INSERT goods values (0,3,'Мотороллер «Муравей-1»');
INSERT goods values (0,1,'«Десна»');
INSERT goods values (0,1,'«Салют»');
INSERT goods values (0,3,'Мотороллер «Муравей-2»');
INSERT goods values (0,0,'Автомобили');
INSERT goods values (0,11,'Отечественные');
INSERT goods values (0,11,'Импортные');
INSERT goods values (0,13,'Opel');
INSERT goods values (0,13,'Mazda');
INSERT goods values (0,12,'ВАЗ');
INSERT goods values (0,13,'BMW');
INSERT goods values (0,16,'ВАЗ2101');

Получаем таблицу goods

Id Id_group Name
1 0 Велосипеды
2 0 Мопеды
3 0 Мотороллеры
4 1 «Школьник»
5 2 Мопед 2-х тактовый
6 2 Мопед 4-х тактовый
7 3 Мотороллер «Муравей-1»
8 1 «Десна»
9 1 «Салют»
10 3 Мотороллер «Муравей-2»
11 0 Автомобили
12 11 Отечественные
13 11 Импортные
14 13 Opel
15 13 Mazda
16 12 ВАЗ
17 13 BMW
18 16 ВАЗ2101

Функция

Для сортировки будем использовать составной ключ, который получим с помощью функции get_key:
В функцию передаются 2 параметра: id и tip.
id – код,
tip – определяет порядок построения составного ключа (0 – по id кодам, 1 – по наименованиям).

CREATE DEFINER = 'root'@'%' FUNCTION `get_key`(id_good INTEGER(11), tip TINYINT(1))
RETURNS varchar(250) CHARSET cp1251
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE id_gr integer(9);
      DECLARE ext_key varchar(250);
      IF tip=0 then
          set ext_key = CAST(id_good as CHAR);
      else
          set ext_key = (select trim(`name`) FROM goods where id=id_good);
      end if;
      set id_gr= (select id_group FROM goods where id=id_good);
      IF id_gr<>0 THEN
          REPEAT
          IF tip=0 then
              set ext_key = CONCAT(cast(id_gr as CHAR),'/',ext_key);
          else
              set ext_key = CONCAT((select trim(`name`) FROM goods where id=id_gr),'/',ext_key);
          end if;
          set id_gr= (select id_group FROM goods where id=id_gr);
          UNTIL id_gr=0
          END REPEAT;
      END IF;
      RETURN ext_key;
END;

Запрос на построение дерева c сортировкой по id:

select * from (select *, `get_key`(gs.`id`,0) as e_k from goods gs) t order by t.e_k
 

id id_group name e_k
1 0 Велосипеды 1
4 1 «Школьник» 1/4
8 1 «Десна» 1/8
9 1 «Салют» 1/9
11 0 Автомобили 11
12 11 Отечественные 11/12
16 12 ВАЗ 11/12/16
18 16 ВАЗ2101 11/12/16/18
13 11 Импортные 11/13
14 13 Opel 11/13/14
15 13 Mazda 11/13/15
17 13 BMW 11/13/17
2 0 Мопеды 2
5 2 Мопед 2-х тактовый 2/5
6 2 Мопед 4-х тактовый 2/6
3 0 Мотороллеры 3
10 3 Мотороллер «Муравей-2» 3/10
7 3 Мотороллер «Муравей-1» 3/7

Запрос на построение дерева c сортировкой по name:

select * from (select *, `get_key`(gs.`id`,1) as e_k from goods gs) t order by t.e_k

id id_group name e_k
11 0 Автомобили Автомобили
13 11 Импортные Автомобили/Импортные
17 13 BMW Автомобили/Импортные/BMW
15 13 Mazda Автомобили/Импортные/Mazda
14 13 Opel Автомобили/Импортные/Opel
12 11 Отечественные Автомобили/Отечественные
16 12 ВАЗ Автомобили/Отечественные/ВАЗ
18 16 ВАЗ2101 Автомобили/Отечественные/ВАЗ/ВАЗ2101
1 0 Велосипеды Велосипеды
8 1 «Десна» Велосипеды/«Десна»
9 1 «Салют» Велосипеды/«Салют»
4 1 «Школьник» Велосипеды/«Школьник»
2 0 Мопеды Мопеды
5 2 Мопед 2-х тактовый Мопеды/Мопед 2-х тактовый
6 2 Мопед 4-х тактовый Мопеды/Мопед 4-х тактовый
3 0 Мотороллеры Мотороллеры
7 3 Мотороллер «Муравей-1» Мотороллеры/Мотороллер «Муравей-1»
10 3 Мотороллер «Муравей-2» Мотороллеры/Мотороллер «Муравей-2»

В принципе по количеству разделителей (в данном случае «/») можно определить уровень для каждого элемента при построении дерева.



  MySQL     11 Февраля, Пятница 
 
Ajax CSS JavaScript Google php 1C MSSQL Маркетинг MySQL Web-разработка SEO Virus Заработок Логотипы Фирменный стиль Безопасность SMO Хостинг Аутсорсинг Сотовая связь Домены

 
 
 
© 2006-2016 Веб-студия «Софт-Арт»
Создание сайтов в Ростове
(863) 2-098-456
support@soft-art.ru