选择属于层次结构中任何类别的类别的产品

Question

选择属于层次结构中任何类别的类别的产品

8

我有一个产品表，其中包含一个指向类别的外键。类别表是按照一定方式创建的，每个类别都可以有一个父类别，例如：

Computers
    Processors
        Intel
            Pentium
            Core 2 Duo
        AMD
            Athlon

我需要制作一个查询，如果选定的类别是处理器，它将返回在Intel、奔腾、Core 2 Duo、Amd等中的产品... 我考虑创建某种“缓存”，它将为数据库中每个类别存储层次结构中的所有类别，并在where子句中包括“IN”。这是最好的解决方案吗？

- Bruno

所有的答案投票都去哪了？ - MDCore

9个回答

4

看起来需要使用公用表达式（Common Table Expression）来解决，大概是这样的：

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

这将选择名称为“处理器”的类别及其任何后代，可以在IN子句中使用它来获取产品。

- Beepcake

0

我以前做过类似的事情，首先查询类别ID，然后查询“IN”这些类别的产品。获取类别是最难的部分，你有几个选择：

如果类别嵌套的级别已知或者你可以找到一个上限：构建一个看起来很可怕的SELECT语句，其中包含大量的JOIN操作。这很快，但很丑陋，而且你需要在层次结构的级别上设置一个限制。
如果你总共只有相对较少的类别，请查询它们所有（仅ID、父级），收集你关心的那些ID，并为产品执行SELECT....IN。这对我来说是合适的选项。
使用一系列SELECT向上/向下查询层次结构。简单，但相对较慢。
我相信SQLServer的最新版本支持递归查询，但我自己没有使用过。

如果你不想在应用程序端进行操作，存储过程可以帮助你。

- Draemon

0

我之前回答另一个问题的答案同样适用于这里... SQL中的递归

我在链接的书中提到了一些方法，应该可以很好地解决你的情况。

- Tom H.

0

你想要找到的是类别“parent”关系的传递闭包。我想没有类别层次结构深度的限制，所以你不能制定一个单一的SQL查询来查找所有类别。我会这样做（用伪代码表示）：

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

因此，只需继续查询子项，直到找不到为止。除非您有一个退化的层次结构（例如，1000个类别，每个类别都是另一个类别的子项），或者总类别数量很大，否则这种行为在速度方面表现良好。在第二种情况下，您可以始终使用临时表来保持应用程序和数据库之间的数据传输量较小。

- Simon

0

也许是类似这样的东西：

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)

[编辑] 如果类别深度大于1，则这将形成您最内部的查询。我怀疑您可以设计一个存储过程，该存储过程会向下钻取表格，直到内部查询返回的ID没有子项为止--可能更好的方法是具有将类别标记为层次结构中的终端节点的属性--然后对这些ID执行外部查询。

- tvanfosson

没错，但是在我的数据库架构中，类别深度没有限制...也许“缓存”解决方案现在是最好的选择..无论如何，谢谢！ - Bruno

0

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

实际上，如果您直接运行示例的最后一部分，则不起作用。只需从产品中删除选择并替换为简单的SELECT * FROM r即可。

- Kaboing

0

这应该从给定的类别开始递归下所有“子”类别。

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

- Should_be_working

0

我喜欢使用堆栈临时表来处理分层数据。以下是一个简单的示例 -

-- 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

这里有一个很好的解释链接文本

- cheeves

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MDCore · Accepted Answer

在数据库设计阶段，最好的解决方案是使用嵌套集（Nested Set）来设计您的分类表。文章《Managing Hierarchical Data in MySQL》（MySQL 中管理分层数据）虽然标题中带有 MySQL，但实际上适用于各种数据库，并且提供了在数据库表中存储层次结构的不同方法的概述。

执行摘要：

嵌套集

任意深度的选择操作都很容易
插入和删除操作很困难

基于标准 parent_id 的层次结构

选择操作基于内部连接（因此会变得复杂）
插入和删除操作很容易

因此，根据您的示例，如果您的层次结构表是一个嵌套集，您的查询将类似于以下内容：

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

“2”和“11”分别是处理器记录的左侧和右侧。