Perguntas[hierarchy](dba)

Tenho uma tabela construída no modelo de conjunto aninhado que basicamente registra transações de diferentes naturezas (definidas pelo campo idx):

pk  lft rgt idx             value
 1   1  30  2783     20402.710000
 2   2   3  2783    -13885.670000
 3   4   5   131        78.547946
 4   6   7  2783      -586.810000
 5   8   9     1         0.013421
 6  10  11  2783     -1777.630000
 7  12  13   873     10791.237266
 8  14  15  2783       -72.510000
 9  16  17  1697       128.626280
10  18  19  2783      -600.430000
11  20  21  1168         6.949175
12  22  23  2783     -2479.080000
13  24  25  1758       853.502787
14  26  27  2783     -1000.580000
15  28  29  3794      3172.428304
16  31  40  2783       615.940000
17  32  33  2783      -615.940000
18  34  39     1         0.040390
19  35  36     1        -0.000152
20  37  38     1        -0.000300
21  41  42  2783      3012.050000

Como essas transações são aninhadas de uma maneira particular, onde um nó filho pode ser de natureza diferente de seu pai (representando uma espécie de "transformação" ao longo do caminho), estou tendo dificuldade em buscar o "saldo" para cada pai nó em sua própria natureza :

prnt_pk      idx            aggr
1           2783        0.000000
3            131       78.547946
5              1        0.013421
7            873    10791.237266
9           1697      128.626280
11          1168        6.949175
13          1758      853.502787
15          3794     3172.428304
16          2783        0.000000
18             1        0.039938
21          2783     3012.050000

Como eu poderia alcançar essa saída desejada dada a tabela original? Eu tentei fazer algo ao longo das linhas descritas nesta pergunta , mas tudo o que consegui foi um único nó pai e todos os seus filhos com o mesmo idx, o que eu poderia fazer mais facilmente no meu caso SELECT * FROM table WHERE idx = 2783ou similar.

Caso ajude, cada nó pai tem um valor positivo, nenhum nó com valores negativos pode ter filhos.

Imagine uma tabela com três colunas: uma chave primária de ID, um ParentID anulável referenciando a coluna de ID nessa mesma tabela e um TreeID para indicar os registros que compõem uma determinada árvore. Uma estrutura hierárquica básica pode ser assim. Observe que os valores ordinais não necessariamente afetam a posição de um registro na hierarquia.

      dbo.TreeNodes             Visual representation:
+----+----------+--------+    --------------------------
| ID | ParentID | TreeID |       Tree 7:     Tree 15:
+----+----------+--------+       1           10
|  1 | NULL     |      7 |       |–3         |–9
|  2 | 4        |      7 |       |–4
|  3 | 1        |      7 |       |–|–2
|  4 | 1        |      7 |       |-5
|  5 | 1        |      7 |
|  9 | 10       |     15 |
| 10 | NULL     |     15 |
+----+----------+--------+

Dada outra tabela não relacionada com uma definição idêntica ( dbo.TreeNodes2, digamos), descobri que copiar os dados da primeira tabela para a segunda mantendo sua hierarquia auto-referencial não é tão simples. Eu fico preso depois de inserir os novos registros dbo.TreeNodes2 quando chega a hora de preencher os valores ParentID. Eu teria que ter algum tipo de tabela de mapeamento para saber quais registros da primeira tabela mapeiam para os novos registros da segunda tabela. Aqui está o que eu tenho até agora:

DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

Eu poderia adicionar uma OldIDcoluna não relacionada a dbo.TreeNodes2 para copiar os IDs originais, que eu poderia usar para criar um mapeamento para ingressar e preencher os novos registros ParentID e, em seguida, anular todos os OldIDvalores quando terminar, mas isso parece um design ruim. Se o SQL Server permitisse que a cláusula OUTPUT de um INSERT incluísse colunas de uma junção, isso seria fácil, mas toda a documentação , bem como meus próprios testes, indicam que apenas Inserted.*colunas, Deleted.*colunas e valores/literais escalares podem ser usado na cláusula OUTPUT. Nada de junções.

Meu sonho, T-SQL inválido que faz o que eu quero, seria algo assim:

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

A partir daqui, eu teria uma tabela de mapeamento que posso usar para preencher os valores ParentID na nova tabela.

Existe um padrão para entregar esses tipos de inserções hierárquicas entre tabelas sem depender de uma coluna extra cujo único objetivo é permitir a migração, após o que é uma coluna irrelevante?

Tenho no campo cod_categorizacao_pai o ID do pai, e preciso montar um select que basta digitar um ID e ele listará os IDs paternos desse ID até chegar no pai de todos. Como faço para definir um select para fazer isso? Vou precisar de um procedimento? Estou usando PL/SQL.

A mesa:

CREATE TABLE [dbo].[menu]
(
  id INT PRIMARY KEY
, name NVARCHAR(20)
, parent_id INT REFERENCES [dbo].[menu] ([id])
);

Suponha que eu esteja usando os registros abaixo:

INSERT INTO menu VALUES (1,'Main Product',NULL); 
INSERT INTO menu VALUES (2,'Next Main Product',NULL); 
INSERT INTO menu VALUES (3,'Addon Product',1); 
INSERT INTO menu VALUES (4,'Addon Product',1); 
INSERT INTO menu VALUES (5,'Sub-Addon Product',3);
INSERT INTO menu VALUES (6,'Sub-sub-Addon Product',5);
INSERT INTO menu VALUES (7,'Addon Next Main Product',2);

Eu gostaria de ter a seguinte lista se eu passasse o ID '6' como parâmetro: 1,3,5,6

Eu tenho as seguintes tabelas dentro de um banco de dados:

+-------------+   +-------------+-------------+   +---------------------------+
|  project1   |   |          project2         +   |         project3          |
+-------------+   +-------------+-------------+   +---------------------------+
| project1_id |   | project2_id | project1_id |   | project3_id | project2_id |
+-------------+   +-------------+-------------+   +---------------------------+
|    hnc1     |   |    hnc4     |     hnc1    |   |     hnc7    |    hnc4     |
|    hnc2     |   |    hnc5     |     hnc1    |   |     hnc8    |    hnc4     |
|    hnc3     |   |    hnc6     |     hnc3    |   |     hnc9    |    hnc6     |
+-------------+   +-------------+-------------+   +---------------------------+

Eu preciso recuperar todos os descendentes de um determinado ID de todas as três tabelas - incluindo o próprio ID, se ele for encontrado em qualquer tabela.

Por exemplo, se eu consultar usando o ID 'hnc4' , ele deve obter:

+-------------+
| descendants |
+-------------+
|    hnc7     |
|    hnc8     |
|    hnc4     |
+-------------+

Se eu consultar usando o ID 'hnc1' :

+-------------+
| descendants |
+-------------+
|    hnc4     |
|    hnc5     |
|    hnc7     |
|    hnc8     |
|    hnc1     |
+-------------+

A consulta deve funcionar mesmo que o ID esteja apenas na última tabela project3.

Eu estava pesquisando e descobri que um CTE recursivo poderia ser útil, mas não sei como fazê-lo com 3 tabelas envolvidas.

Alguma orientação sobre como obter os resultados certos?

Estou projetando um banco de dados para estudantes. Estudantes de ciência da computação aqui na Suíça podem decidir entre diferentes especializações, por exemplo, desenvolvimento de aplicativos, engenharia de sistemas, etc.

Também há alunos de outras profissões neste aplicativo, que não possuem essa especialização. Alguns exemplos:

+--------------------+-------------------------+
| Profession         | Specialization          |
+--------------------+-------------------------+
| Computer scientist |                         |
|                    | Application development |
|                    | System engineering      |
|                    | Support                 |
|                    |                         |
| Electrician        |                         |
|                    | none                    |
|                    |                         |
| Janitor            |                         |
|                    | none                    |
|                    |                         |
| Architect          |                         |
|                    | Small buildings         |
|                    | High buildings          |
|                    |                         |
+--------------------+-------------------------+

Espero que você tenha a idéia. Minha pergunta agora é, como eu desenho as tabelas do banco de dados com esses atributos, já que eles são dependentes uns dos outros? Cada usuário tem uma profissão, alguns não possuem uma especialização dependendo de sua profissão. Portanto, um eletricista não deve ser um desenvolvedor de aplicativos, nem um arquiteto.

Meus pensamentos até agora 1

+-----------------------------+
| User                        |
+-----------------------------+
| #id                         |
| profession_id               |
| specialisazion_id, nullable |
+-----------------------------+

Imponha a lógica por meio de verificações de restrição

Abordagem 2

+-------------------+     +----------------+     +------------+
| User              |     | Specialization |     | Profession |
+-------------------+     +----------------+     +------------+
| #id               |  +--| #id            |  +--| #id        |
| username          |  |  | name           |  |  | name       |
| specialization_id |--+  | profession_id  |--+  +------------+
+-------------------+     +----------------+

Gerencie eu mesmo a lógica e garanta que toda profissão sem especialização tenha uma entrada de especialização.

Abordagem 3

+----------------+     +-----------------------------+     +------------+
| Specialization |     | spec_prof                   |     | Profession |
+----------------+     +-----------------------------+     +------------+
| #id            |--+  | #id                         |  +--| #id        |
| name           |  +--| specialization_id, nullable |  |  | name       |
+----------------+     | profession_id               |--+  +------------+
                       +-----------------------------+
                                                |
                                                |                                         
                              +--------------+  |
                              | User         |  |
                              +--------------+  |
                              | #id          |  |
                              | username     |  |
                              | spec_prof_id |--+
                              +--------------+

De alguma forma, todas as abordagens diferentes parecem desajeitadas, sujas. Quais são os argumentos a favor e contra as diferentes abordagens? Existe uma maneira melhor?

E como faço para procurar esse problema? Dependente é a nomenclatura correta?

Qualquer ajuda é muito apreciada.

Alguém pode me explicar a diferença entre as duas perguntas a seguir? Apesar de parecerem iguais, os resultados são diferentes.

select concept.concept_id, concept.PARENT_ID 
from ebti_thes_concept_v concept
  start with PARENT_ID = '11025'
  connect by parent_id = prior concept_id
  and exists (..) 

Na segunda, o existspredicado é movido da connect bycláusula para a wherecláusula.

select concept.concept_id, concept.PARENT_ID
from ebti_thes_concept_v concept
where exists (..)
 start with PARENT_ID = '11025'
 connect by parent_id = prior concept_id;

Estou usando o MySQL (5.7.20) para armazenar uma hierarquia de pastas. Cada pasta é armazenada junto com todos os pais diretos e indiretos incluindo a distância. Como exemplo, as seguintes pastas

A
+- B
   + C
   + D

seria armazenado assim:

parent | child | dist
-------+-------+-----
A      | A     | 0
A      | B     | 1
A      | C     | 2
A      | D     | 2
B      | B     | 0
B      | C     | 1
B      | D     | 1
C      | C     | 0
D      | D     | 0

Além disso, há outra tabela que vincula uma pasta a um ou vários usuários, como esta:

child | user
------+-----
A     | U1
B     | U1
C     | U2
D     | U2

O que estou tentando fazer agora é consultar essa hierarquia por usuário de forma que apenas as correspondências mais altas na hierarquia sejam retornadas.

Então, como no exemplo acima, se eu consultar as pastas de user U1, eu só quero Acomo resultado, porque a segunda correspondência Bé um filho de A. Por outro lado, se eu consultar as pastas de user U2, o resultado deve ser C, Dque ambas as pastas estejam no mesmo nível de hierarquia.

A consulta mais próxima que eu poderia imaginar foi SELECT child, dist AS d FROM folders JOIN users ON folders.child = users.child GROUP BY folders.parent HAVING d = min(d);, mas infelizmente não está dando os resultados corretos, pois, por exemplo, uma consulta para o usuário U1ainda daria pastas A,Bcomo resultado.

Alguém tem ideia de como isso pode ser feito?

Estou tentando reproduzir o primeiro exemplo simples deste tutorial .

No entanto, não entendo por que recebo tantas linhas de volta. Eu esperaria que 1 reportasse a 2 apenas no nível 1 e não no nível 2 também.

SQL> select employee_id, reports_to from employees;

EMPLOYEE_ID REPORTS_TO
----------- ----------
      1      2
      2
      3      2
      4      2
      5      2
      6      5
      7      5
      8      2
      9      5

9 rows selected.

SQL> select employee_id, reports_to, LEVEL from employees connect by prior employee_id = reports_to;

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      1      2      1
      8      2      1
      5      2      1
      6      5      2
      9      5      2
      7      5      2
      4      2      1
      3      2      1
      6      5      1
      9      5      1
      7      5      1

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      2         1
      1      2      2
      8      2      2
      5      2      2
      6      5      3
      9      5      3
      7      5      3
      4      2      2
      3      2      2

20 rows selected.

Eu tenho uma tabela na qual as categorias são listadas e cada produto pode pertencer a uma categoria pai ou não. Existe apenas um nível de profundidade possível.

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Em seguida, insiro alguns registros:

INSERT INTO `categories` (`id`, `name`, `parent`) VALUES
(1, 'Cat 1',    NULL),
(2, 'Cat 2',    NULL),
(3, 'Cat 3',    NULL),
(4, 'Subcat 2.1',   2),
(5, 'Subcat 2.2',   2);

Agora eu gostaria de obter uma lista que inclua todas as categorias e subcategorias, ordenadas por categoria principal e com as subcategorias abaixo, assim:

Cat 1
Cat 2
Subcat 2.1
Subcat 2.2
Cat 3

Quando tento o seguinte quase funciona, mas não retorna o registro de "Cat 2", apenas filhos.

SELECT e.name AS parent_name, e.id AS parent_id, r.id AS child_id, r.name AS child_name
     FROM categories e
LEFT JOIN categories r ON e.id = r.parent
    WHERE e.parent IS NULL 
 ORDER BY parent_name, child_name

Resultados:

Cat 1   1   NULL    NULL
Cat 2   2   4   Subcat 2.1
Cat 2   2   5   Subcat 2.2
Cat 3   3   NULL    NULL

Como posso fazer com que ele retorne todos os 5 registros, incluindo a categoria pai "Cat 2"?

NºEmp |Designação | ReportsTo | Quarto
10 | Diretor | NULO | 1
20 | Gerente Sênior | 10 | 2
30 | Datilógrafo | 20 | (ESTE CARA SE REFERE AO EMpno 10 room)
40 | Programador | 30 | 4

Como faço para que o quarto do EmpNo 30 se refira ao chefe dele, mas não faça o EmpNo 40 se referir ao chefe dele?