sql - tabla - ejemplos de consultas en la base de datos northwind

Seleccione productos donde la categoría pertenece a cualquier categoría en la jerarquía

Question

Tengo una tabla de productos que contiene un FK para una categoría, la tabla Categorías se crea de forma que cada categoría puede tener una categoría principal, por ejemplo:

Computers
    Processors
        Intel
            Pentium
            Core 2 Duo
        AMD
            Athlon

Necesito hacer una consulta selectiva de que si la categoría seleccionada es Procesadores, devolverá productos que están en Intel, Pentium, Core 2 Duo, Amd, etc.

Pensé en crear algún tipo de "caché" que almacenará todas las categorías en la jerarquía para cada categoría en el archivo db e incluir el "IN" en la cláusula where. ¿Es esta la mejor solución?

Answer 1

Esto debería recurrir a todas las categorías de "niños" a partir de una categoría determinada.

DECLARE @startingCatagoryId int
DECLARE @current int
SET @startingCatagoryId = 13813 -- or whatever the CatagoryId is for ''Processors''

CREATE TABLE #CatagoriesToFindChildrenFor
(CatagoryId int)

CREATE TABLE #CatagoryTree
(CatagoryId int)

INSERT INTO #CatagoriesToFindChildrenFor VALUES (@startingCatagoryId)

WHILE (SELECT count(*) FROM #CatagoriesToFindChildrenFor) > 0
BEGIN
    SET @current = (SELECT TOP 1 * FROM #CatagoriesToFindChildrenFor)

    INSERT INTO #CatagoriesToFindChildrenFor
    SELECT ID FROM Catagory WHERE ParentCatagoryId = @current AND Deleted = 0

    INSERT INTO #CatagoryTree VALUES (@current)
    DELETE #CatagoriesToFindChildrenFor WHERE CatagoryId = @current
END

SELECT * FROM #CatagoryTree ORDER BY CatagoryId

DROP TABLE #CatagoriesToFindChildrenFor
DROP TABLE #CatagoryTree

Answer 2

Parece un trabajo para Common Table Expression ... algo parecido a:

with catCTE (catid, parentid)
as
(
select cat.catid, cat.catparentid from cat where cat.name = ''Processors''
UNION ALL
select cat.catid, cat.catparentid from cat inner join catCTE on cat.catparentid=catcte.catid
)
select distinct * from catCTE

Eso debería seleccionar la categoría cuyo nombre es ''Procesadores'' y cualquiera de sus descendientes, debería ser capaz de usar eso en una cláusula IN para retirar los productos.

Answer 3

He hecho cosas similares en el pasado, primero preguntando por los identificadores de categoría, luego preguntando por los productos "EN" esas categorías. Obtener las categorías es lo más difícil, y tienes algunas opciones:

• Si conoce el nivel de anidamiento de las categorías o puede encontrar un límite superior: cree un SELECT de aspecto horrible con muchas JOIN. Esto es rápido pero feo y debe establecer un límite en los niveles de la jerarquía.
• Si tiene un número relativamente pequeño de categorías en total, indague todas (solo ids, padres), recopile los identificadores de los que le interesan y haga un SELECT .... IN para los productos. Esta fue la opción adecuada para mí.
• Consulta arriba / abajo la jerarquía usando una serie de SELECT. Simple, pero relativamente lento.
• Creo que las versiones recientes de SQLServer tienen soporte para consultas recursivas, pero no las he usado yo mismo.

Los procedimientos almacenados pueden ser útiles si no desea hacer esta aplicación.

Answer 4

Lo que quiere encontrar es el cierre transitivo de la relación de categoría "padre". Supongo que no hay ninguna limitación para la profundidad de la jerarquía de categorías, por lo que no puede formular una sola consulta SQL que encuentre todas las categorías. Lo que haría (en pseudocódigo) es esto:

categoriesSet = empty set
while new.size > 0:
  new = select * from categories where parent in categoriesSet
  categoriesSet = categoriesSet+new

Así que sigue preguntando por los niños hasta que no se encuentren más. Esto se comporta bien en términos de velocidad a menos que tengas una jerarquía degenerada (digamos, 1000 categorías, cada una es hija de otra), o un gran número de categorías totales. En el segundo caso, siempre puede trabajar con tablas temporales para mantener la transferencia de datos entre su aplicación y la base de datos pequeña.

Answer 5

Tal vez algo así como:

select *
from products
where products.category_id IN
  (select c2.category_id 
   from categories c1 inner join categories c2 on c1.category_id = c2.parent_id
   where c1.category = ''Processors''
   group by c2.category_id)

[EDITAR] Si la profundidad de la categoría es mayor que uno, esto formaría su consulta más interna. Sospecho que podría diseñar un procedimiento almacenado que profundizaría en la tabla hasta que los ids devueltos por la consulta interna no tuvieran hijos, probablemente sea mejor tener un atributo que marque una categoría como un nodo terminal en la jerarquía, luego realizar la consulta externa en esos identificadores.

Answer 6

CREATE TABLE #categories (id INT NOT NULL, parentId INT, [name] NVARCHAR(100))
INSERT INTO #categories
    SELECT 1, NULL, ''Computers''
    UNION
SELECT 2, 1, ''Processors''
    UNION
SELECT 3, 2, ''Intel''
    UNION
SELECT 4, 2, ''AMD''
    UNION
SELECT 5, 3, ''Pentium''
    UNION
SELECT 6, 3, ''Core 2 Duo''
    UNION
SELECT 7, 4, ''Athlon''
SELECT * 
    FROM #categories
DECLARE @id INT
    SET @id = 2
            ; WITH r(id, parentid, [name]) AS (
    SELECT id, parentid, [name] 
        FROM #categories c 
        WHERE id = @id
        UNION ALL
    SELECT c.id, c.parentid, c.[name] 
        FROM #categories c  JOIN r ON c.parentid=r.id
    )
SELECT * 
    FROM products 
    WHERE p.productd IN
(SELECT id 
    FROM r)
DROP TABLE #categories   

La última parte del ejemplo no funciona realmente si lo está ejecutando de esta manera. Solo elimine la selección de los productos y sustitúyala con un simple SELECT * FROM r

Answer 7

Me gusta usar una tabla de temperatura de pila para datos jerárquicos. aquí hay un ejemplo aproximado:

-- create a categories table and fill it with 10 rows (with random parentIds)
CREATE TABLE Categories ( Id uniqueidentifier, ParentId uniqueidentifier )
GO

INSERT
INTO   Categories
SELECT NEWID(),
       NULL 
GO

INSERT
INTO   Categories
SELECT   TOP(1)NEWID(),
         Id
FROM     Categories
ORDER BY Id
GO 9


DECLARE  @lvl INT,            -- holds onto the level as we move throught the hierarchy
         @Id Uniqueidentifier -- the id of the current item in the stack

SET @lvl = 1

CREATE TABLE #stack (item UNIQUEIDENTIFIER, [lvl] INT)
-- we fill fill this table with the ids we want
CREATE TABLE #tmpCategories (Id UNIQUEIDENTIFIER)

-- for this example we’ll just select all the ids 
-- if we want all the children of a specific parent we would include it’s id in
-- this where clause
INSERT INTO #stack SELECT Id, @lvl FROM Categories WHERE ParentId IS NULL

WHILE @lvl > 0
BEGIN -- begin 1

      IF EXISTS ( SELECT * FROM #stack WHERE lvl = @lvl )
      BEGIN -- begin 2

      SELECT @Id = [item]
      FROM #stack
      WHERE lvl = @lvl

      INSERT INTO #tmpCategories
      SELECT @Id

      DELETE FROM #stack
      WHERE lvl = @lvl
      AND item = @Id

      INSERT INTO #stack
      SELECT Id, @lvl + 1
      FROM   Categories
      WHERE  ParentId = @Id

      IF @@ROWCOUNT > 0
      BEGIN -- begin 3
         SELECT @lvl = @lvl + 1
      END -- end 3
   END -- end 2
   ELSE
   SELECT @lvl = @lvl - 1

END -- end 1

DROP TABLE #stack

SELECT * FROM #tmpCategories
DROP TABLE #tmpCategories
DROP TABLE Categories

hay una buena explicación aquí texto del enlace

Answer 8

La mejor solución para esto es en la etapa de diseño de la base de datos. Su tabla de categorías debe ser un conjunto anidado . El artículo Managing Hierarchical Data en MySQL no es tan específico de MySQL (a pesar del título), y brinda una excelente descripción general de los diferentes métodos para almacenar una jerarquía en una tabla de base de datos.

Resumen ejecutivo:

Conjuntos anidados

• Las selecciones son fáciles para cualquier profundidad
• Las inserciones y eliminaciones son difíciles

Jerarquía estándar basada en parent_id

• Las selecciones se basan en uniones internas (así que ponte peludo rápido)
• Inserciones y eliminaciones son fáciles

Por lo tanto, en función de su ejemplo, si su tabla de jerarquía era un conjunto anidado, su consulta se vería así:

SELECT * FROM products 
   INNER JOIN categories ON categories.id = products.category_id 
WHERE categories.lft > 2 and categories.rgt < 11

el 2 y el 11 son a la izquierda y a la derecha respectivamente del registro de Processors .

Answer 9

Mi respuesta a otra pregunta de hace un par de días se aplica aquí ... recursividad en SQL

Hay algunos métodos en el libro que he vinculado que deberían cubrir su situación muy bien.