Spłaszczenie Hierarchii Listy Pomocniczej Do Listy Wszystkich Ścieżek

Jak wspomniano, SQL nie ma czystego sposobu na implementację tabel z dynamicznie zmieniającą się liczbą kolumn. Jedyne dwa rozwiązania, z których korzystałem wcześniej, to: 1. Stała liczba Samorozłączeń, dająca stałą liczbę kolumn (jak na BobInce) 2. Generowanie wyników w postaci ciągu znaków w jednej kolumnie

Drugi brzmi groteskowo na początku; przechowywanie identyfikatorów jako ciąg znaków?! Ale kiedy wyjście jest sformatowane jako XML lub coś takiego, ludzie nie mają nic przeciwko tak bardzo.

Podobnie, jest to bardzo niewielkie użycie, jeśli chcesz dołączyć do wyników w SQL. Jeśli wynik ma być dostarczony do aplikacji, może być bardzo odpowiedni. Osobiście jednak wolę zrobić spłaszczenie w aplikacji, a nie SQL

Utknąłem tutaj na 10 calowym ekranie bez dostępu do SQL, więc nie mogę podać testowanego kodu, ale podstawową metodą byłoby wykorzystanie rekurencji w jakiś sposób;
- Funkcja skalarna rekurencyjna może to zrobić
- MS SQL może to zrobić za pomocą rekurencyjnego z statement (more efficient)

Funkcja skalarna (coś w rodzaju):

CREATE FUNCTION getGraphWalk(@child_id INT)
RETURNS VARCHAR(4000)
AS
BEGIN

  DECLARE @graph VARCHAR(4000)

  -- This step assumes each child only has one parent
  SELECT
    @graph = dbo.getGraphWalk(parent_id)
  FROM
    mapping_table
  WHERE
    category_id = @child_id
    AND parent_id IS NOT NULL

  IF (@graph  IS NULL)
    SET @graph = CAST(@child_id AS VARCHAR(16))
  ELSE
    SET @graph = @graph + ',' + CAST(@child_id AS VARCHAR(16))

  RETURN @graph

END


SELECT
  category_id                         AS [category_id],
  dbo.getGraphWalk(category_id)       AS [graph_path]
FROM
  mapping_table
ORDER BY
  category_id

Dawno nie używałem rekurencyjnego z, ale podam składnię, mimo że nie mam tu SQL ' a do testowania czegokolwiek:)

Rekurencyjny z

WITH
  result (
    category_id,
    graph_path
  )
AS
(
  SELECT
    category_id,
    CAST(category_id AS VARCHAR(4000))
  FROM
    mapping_table
  WHERE
    parent_id IS NULL

  UNION ALL

  SELECT
    mapping_table.category_id,
    CAST(result.graph_path + ',' + CAST(mapping_table.category_id AS VARCHAR(16)) AS VARCHAR(4000))
  FROM
    result
  INNER JOIN
    mapping_table
      ON result.category_id = mapping_table.parent_id
)

SELECT
  *
FROM
  result
ORDER BY
  category_id

EDIT-OUTPUT dla obu jest taki sam:

1   '1'
2   '1,2'
3   '1,2,3'
4   '1,2,4'
5   '1,2,5'
6   '1,6'
7   '1,6,7'
8   '1,6,7,8'
9   '1,6,9'

Przemierzanie drzewa o dowolnej głębokości zazwyczaj wymaga rekurencyjnego kodu proceduralnego, chyba że korzystasz ze specjalnych funkcji niektórych DBMS.

W Oracle klauzula CONNECT BY pozwoli Ci przejść głębiej drzewo w pierwszej kolejności, jeśli użyjesz listy przyległości, tak jak to zrobiłeś tutaj.

Jeśli używasz zagnieżdżonych zestawów, lewy numer sekwencji zapewni Ci kolejność odwiedzania węzłów.

W rzeczywistości można to zrobić za pomocą dynamicznego SQL w ramach procedury stores. Następnie stają się ograniczone do tego, co można zrobić sith procedury składowanej. Oczywiście staje się wyzwaniem, aby EXEC wyniki do tymczasowej tabeli nie wiedząc, ile kolumn się spodziewać. Jeśli jednak celem jest wyjście na stronę internetową lub inny interfejs użytkownika, może to być warte wysiłku...