Форум пользователей MySQL

avk001 · 27.05.2011 13:10:21

Добрый день.

Столкнулся с такой проблемой.
Есть таблица tree

`id` int(15) NOT NULL,
`name` varchar(150) CHARACTER DEFAULT NULL,
`prev_id` int(15) DEFAULT NULL,
`tree` varchar(255) CHARACTER DEFAULT NULL,
PRIMARY KEY (`id`)

Поле prev_id содержит ID родителя
Поле tree содержит список id. пример (70, 72)

+----+------------------------------------+---------+--------+
| id | name | prev_id | tree |
+----+------------------------------------+---------+--------+
| 70 | test | 0 | |
| 71 | test 1 | 70 | 70 |
| 72 | test 2 | 70 | 70 |
| 73 | test 1.1 | 71 | 70, 71 |
| 74 | test 1.2 | 71 | 70, 71 |
| 77 | test 2.2 | 72 | 70, 72 |
+----+------------------------------------+---------+--------+

есть 2 запроса:

1) select tree from horse_cat_tree where id=74
+--------+
| tree |
+--------+
| 70, 71 |

2) select name from horse_cat_tree where id in (70,71);
+--------+
| name |
+--------+
| test |
| test 1 |
+--------+

объединяю эти 2 запроса в один
select name from horse_cat_tree where id in (select tree from horse_cat_tree where id=74);
+------+
| name |
+------+
| test |
+------+

в результате получаем 1 строку, а должно быть две, что я сделал не правильно????

paulus · 27.05.2011 15:34:59

Не нужно хранить данные в VARCHAR через запятую. Когда данные
хранятся в таком формате, Вы можете делать выборки только через
клиентское приложение. В данном случае Вы еще и игнорируете пре-
дупреждение, в котором написано, что строка «70,71» приведена к
числу (0).

avk001 · 27.05.2011 17:55:17

Тогда как правильно организовать хранение. Мне нужно получить имена всех родительских узлов, за один проход и в том порядке, в котором записаны идентификаторы в "tree".
Делать несколько вызовов не рентабельно, .

Test (id 2)
Test 1 (id 100)
Test 2 (id 48)
Test 3 (id 99)
Test 4 (id 1)
И .т.д

avk001 · 29.05.2011 12:53:03

Спасибо за оперативный ответ.
Проблему решил следующим образом:

CREATE DEFINER = 'root'@'localhost' FUNCTION `stringSplit`(x varchar(255), delim varchar(12), pos int)

    RETURNS varchar(255) CHARSET latin1

    NOT DETERMINISTIC

    CONTAINS SQL

    SQL SECURITY DEFINER

    COMMENT ''

return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '');

CREATE DEFINER = 'root'@'localhost' FUNCTION `substrCount`(s VARCHAR(255), ss VARCHAR(255))

    RETURNS tinyint(3) unsigned

    NOT DETERMINISTIC

    READS SQL DATA

    SQL SECURITY DEFINER

    COMMENT ''

BEGIN

DECLARE count TINYINT(3) UNSIGNED;

DECLARE offset TINYINT(3) UNSIGNED;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;

SET count = 0;

SET offset = 1;

REPEAT

IF NOT ISNULL(s) AND offset > 0 THEN

SET offset = LOCATE(ss, s, offset);

IF offset > 0 THEN

SET count = count + 1;

SET offset = offset + 1;

END IF;

END IF;

UNTIL ISNULL(s) OR offset = 0 END REPEAT;

RETURN count;

END;

DELIMITER $$

DROP PROCEDURE  IF EXISTS `MyMenu` $$ 

CREATE DEFINER = 'root'@'localhost' PROCEDURE `MyMenu`(num bigint,  delim VARCHAR(10))

BEGIN

DECLARE strX varchar(255);

DECLARE retstr varchar(255);

DECLARE Valcount INT(10);

DECLARE v1 INT(10);

drop temporary table if EXISTS TableTemp ;

create TEMPORARY table TableTemp (num BIGINT) type=heap;

set strX = (select tree from horse_cat_tree where id=num);

SET retstr = '';

SET Valcount = substrCount(strX,delim)+1;

SET v1=0;

WHILE (v1 < Valcount) DO

SET retstr = stringSplit(strX,delim,v1+1);

SET v1 = v1 + 1;

insert into TableTemp values ( CAST(retstr as UNSIGNED));

END WHILE;

 select id, title from horse_cat_tree where id in (select * from TableTemp);

drop temporary table if EXISTS TableTemp;

END$$

DELIMITER ;

Вызов:

 call MuMenu (74, ";");

 

Результат:

+----+--------+

| id | title  |

+----+--------+

| 70 | test   |

| 71 | test 1 |

+----+--------+

Форум пользователей MySQL

#1 27.05.2011 13:10:21

Проблема с IN и вложенным Select

#2 27.05.2011 15:34:59

Re: Проблема с IN и вложенным Select

#3 27.05.2011 17:55:17

Re: Проблема с IN и вложенным Select

#4 29.05.2011 12:53:03

Re: Проблема с IN и вложенным Select

Board footer