Вложенные данные в таблице базы данных

У меня есть таблица, содержащая записи, которые связаны следующим образом:

родитель, ребенок, внук.

Я хотел бы иметь возможность запускать запросы, чтобы делать следующее:

  1. получить все записи, которые произошли от конкретной записи
  2. получить все записи, которые являются прямыми дочерними элементами конкретной записи (то есть только прямыми дочерними элементами)
  3. получить все записи, которые являются внуками определенной записи (т. е. только внуками)
  4. получить дедушку или бабушку записи
  5. получить родительский элемент записи
  6. получить все записи (и их детей и внуков)

Я хочу, чтобы этот запрос возвращал данные в отсортированном виде, чтобы я мог создать структурированный документ (например, XML из результата запроса). Под "отсортированным" я подразумеваю, что набор результатов "ГРУППИРУЕТСЯ" по родительскому элементу ТОГДА дочерний элемент, ТОГДА внук.

Я разработал такую ​​простую таблицу (КОД ПСЕВДО):

CREATE TABLE family_tree {id integer
                    , parent_id integer
                    , child_id integer
                    , fname varchar(16)
                    , lname varchar(32) };

У меня есть ряд вопросов:

  1. (Принимая во внимание запросы, которые я хочу выполнить [(1) - (6) выше]), это лучшая (т.е. самая эффективная структура таблицы, которую я могу использовать? Если нет, то что?

  2. Может ли кто-нибудь помочь мне в написании операторов ANSI SQL для выполнения запросов (1) - (6) выше?


sql ansi-sql
person scoobydoo    schedule 28.09.2009    source источник
comment
id, parentid должно быть достаточно. Вы используете sql server 2005+?   -  person Adriaan Stander    schedule 28.09.2009
comment
поищите в SO такие термины, как «вложенный набор» и «иерархические данные», и вы найдете много всего.   -  person markus    schedule 28.09.2009
comment
Какую базу данных вы используете? Oracle имеет иерархические запросы (CONNECT BY)   -  person Thilo    schedule 28.09.2009
comment
@Thilo - Скуби специально запрашивает ANSI SQL, который исключает расширения поставщиков, такие как Oracle, какими бы крутыми они ни были.   -  person APC    schedule 28.09.2009

Ответы (2)


arrow_upward
2
arrow_downward

Ознакомьтесь с руководством по точке сайта по этому поводу.

  • Рекурсивный метод хранения данных (id, parent_id) позволяет получать прямых потомков конкретного узла.
  • Метод обхода предварительно упорядоченного дерева позволяет получить целую ветвь определенного узла.

Поэтому лучше всего иметь id,parent_id,left,right столбец. Этот учебник содержит весь код, но запросы SQL должны быть достаточно ясными.

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

person Eimantas    schedule 28.09.2009

arrow_upward
0
arrow_downward

Я вижу, вы уже приняли ответ Эймантаса, сказав, что

«Так что лучше всего иметь столбцы id, parent_id, left, right».

Хочу особо указать на следующую цитату из процитированной ссылки (курсив мой):

"Добавление узла

Как добавить узел к дереву? Есть два подхода: вы можете оставить родительский столбец в своей таблице и просто повторно запустить функцию rebuild_tree () - простую, но не очень элегантную функцию; или вы можете обновить левое и правое значения всех узлов с правой стороны нового узла. "

person Erwin Smout    schedule 28.09.2009