设计层次表
可以说我必须将以下信息存储在我的数据库中，
现在我的数据库表将会像这样设计和构造， 
在以后的日子里，如果我不得不添加另一个子类别级别，我将如何能够实现这一点，而不必改变数据库结构呢？
我听说将列定义为表格中的行数据，然后使用枢轴来提取细节......是否是实现此目的的正确方法？
有人可以指导我或指导我正确的方向吗？ 提前致谢...
:)
当要生成新的级别时，向表中添加更多的列会很困难。 最好的方法是使用Hierarchy table来维护Parent-Child relationship 。
表格： Items
x----x------------------x------------x
| ID | Items | CategoryId |
|----x------------------x------------x
| 1 | Pepsi | 3 |
| 2 | Coke | 3 |
| 3 | Wine | 4 |
| 4 | Beer | 4 |
| 5 | Meals | 2 |
| 6 | Fried Rice | 2 |
| 7 | Black Forest | 7 |
| 8 | XMas Cake | 7 |
| 9 | Pinapple Juice | 8 |
| 10 | Apple Juice | 8 |
x----x------------------x------------x
表： Category
在类别表中，您可以将类别添加到n级别。 在Items表中，您可以存储最低级别的类别。 例如，以Pepsi为例 - 其categoryId为3.在Category表中，可以使用JOIN s查找其父项，并使用Hierarchy查询查找父项的父项。
在Category表，用类别ParentId为空（即没有parentId的）将是MainCategory和其他物品ParentId将根据SubCategory 。
编辑：
任何您需要更改表的方式，因为根据您当前的架构，不能将列添加到第一个表，因为子类别的数量可能会不断变化。 即使按照Rhys Jones的答案创建表格，也必须使用字符串连接两个表格。 加入字符串的问题在于，当需要更改Sub category或Main category名称时，必须在每个表格中进行更改，以免将来陷入困境，这不是一个好的数据库设计。 所以我建议你遵循下面的模式。
这是查询，让父母的子项目。
DECLARE @ITEM VARCHAR(30) = 'Black Forest'
;WITH CTE AS
(
-- Finds the original parent for an ITEM ie, Black Forest
SELECT I.ID,I.ITEMS,C.CategoryId,C.Category,ParentId,0 [LEVEL]
FROM #ITEMS I
JOIN #Category C ON I.CategoryId=C.CategoryId
WHERE ITEMS = @ITEM
UNION ALL
-- Now it finds the parents with hierarchy level for ITEM
-- ie, Black Forest. This is called Recursive query, which works like loop
SELECT I.ID,I.ITEMS,C.CategoryId,C.Category,C.ParentId,[LEVEL] + 1
FROM CTE I
JOIN #Category C ON C.CategoryId=I.ParentId
)
-- Here we keep a column to show header for pivoting ie, CATEGORY0,CATEGORY1 etc
-- and keep these records in a temporary table #NEWTABLE
SELECT ID,ITEMS,CATEGORYID,CATEGORY,PARENTID,
'CATEGORY'+CAST(ROW_NUMBER() OVER(PARTITION BY ITEMS ORDER BY [LEVEL] DESC)-1 AS VARCHAR(4)) COLS,
ROW_NUMBER() OVER(PARTITION BY ITEMS ORDER BY [LEVEL] DESC)-1 [LEVEL]
INTO #NEWTABLE
FROM CTE
ORDER BY ITEMS,[LEVEL]
OPTION(MAXRECURSION 0)
这是上述查询的结果
说明
Black Forest在Cake下。 Cake在Bakery 。 Bakery进入Food 。 像这样，您可以创建任意级别的儿童或家长。 现在，如果你想将父母添加到Food和Beverage ，为如Food Industry ，只需添加Food Industry ，以Category表，并保持Food Industry's ID作为ParentId用于Food和Beverage 。 就这样。
现在，如果你想要做透视，你可以按照下面的程序。
1.从列中获取值以将这些值显示为数据透视中的列
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + COLS + ']', '[' + COLS + ']')
FROM (SELECT DISTINCT COLS,[LEVEL] FROM #NEWTABLE) PV
ORDER BY [LEVEL]
2.现在使用下面的PIVOT查询
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM
(
SELECT ITEMS, CATEGORY, COLS
FROM #NEWTABLE
) x
PIVOT
(
MIN(CATEGORY)
FOR COLS IN (' + @cols + ')
) p
ORDER BY ITEMS;'
EXEC SP_EXECUTESQL @query
数据透视后你会得到下面的结果
注意
如果您希望所有记录与项目WHERE ，请删除CTE内的WHERE子句。 点击此处查看结果。
现在我已经提供了数据透视表中的列作为DESC顺序，即它显示顶级父项......项目的父项。 如果您想先显示项目的父项，最后显示下一级和顶级父项，则可以将ROW_NUMBER() DESC更改为ASC 。 点击此处查看结果。
根据你的模式，'主要类别'和'子类别'之间没有关系，但是你的样本数据表明会有一种关系，即酒精是一种饮料等。这听起来像是一个类别的层次结构，在这种情况下，你可以单独的自引用类别表;
create table dbo.Category (
CategoryID int not null constraint PK_Category primary key clustered (CategoryID),
ParentCategoryID int not null,
CategoryName varchar(100) not null
)
alter table dbo.Category add constraint FK_Category_Category foreign key(ParentCategoryID) references dbo.Category (CategoryID)
insert dbo.Category values (1, 1, 'Beverages')
insert dbo.Category values (2, 1, 'Soft Drink')
insert dbo.Category values (3, 1, 'Alcohol')
这样您就可以创建尽可能多的类别级别。 ParentCategoryID = CategoryID的任何类别都是顶级类别。
希望这可以帮助，
里斯
为了添加新的子类别，您应该将该类别添加到表格“ItemSubCategory1”，然后您可以轻松地将其添加到“Drinks”表格中。
例如：如果存在饮料主类别（让CatId = 1，MainCatText ='Beverages'在ItemMainCategory表中）的新类别名称“Hot Drinks”和新项目“Coffee”，则
INSERT INTO ItemSubCategory1(CatId,SubCatText) VALUES(4,'Hot Drinks')
INSERT INTO Drinks(ItemId,ItemName,ItemMainCategory,ItemSubCategory)
VALUES(5,'Coffee',1,4)
上一篇: Designing hierarchy tables
下一篇: Postgresql: How to escape single quotes in Database trigger?