查询对分层数据进行分组和排序 [英] Query to group and sort hierarchical data
问题描述
我的SQL Server 2008中有一个这样的表。
ID ParentID级别代码名称描述
1 1 1 EXP费用 -
2 1 2 PEXP项目Exp -
3 1 2 IEXP间接Exp。 -
4 4 1 INC收入 -
5 1 2 MEXP其他。进出口。 -
6 2 3 MCOST材料成本 -
7 4 2 IINC Indirect Inc. -
8 6 4 TCOS瓷砖成本 -
我想要一个查询，按层次顺序选择所有行。 （物料成本下的瓷砖成本，项目费用下的材料成本，费用下的项目费用等。最多可以有5个等级。表中有2000行。）
这在SQL查询中是否可行？
预期结果应如下所示：
< pre lang =text> ID ParentID级别代码名称描述
1 1 1 EXP费用 -
3 1 2 IEXP间接Exp。 -
5 1 2 MEXP Misc。进出口。 -
2 1 2 PEXP项目Exp -
6 2 3 MCOST材料成本 -
8 6 4 TCOS瓷砖成本 -
4 4 1 INC收入 -
7 4 2 IINC Indirect Inc. -
嗨....
根据表结构，为了获得所需的输出，我认为需要更改表结构和记录的组织方式。
以下是更改： -
1）保持parentid为空的记录，等于id，即Parentid等于ID，在这种情况下ID = 1（费用）和ID = 4（收入）。如果您将来有相似的记录，请确保它们为空。
2）由于每个记录组的级别从1更改或重置，因此创建一个列对其进行分类或分组。例如，在从费用到平铺成本的方案中，它从1-4开始调整，并且对于记录收入和间接记录，级别重置或不同。因此，将其分组/分类为列，并将其称为A，B，C.....类别。
因此表记录以上更改如下： -
ID ParentID级别代码名称类别
1 NULL 1 EXP费用
2 1 2 PEXP项目Exp。 a
3 1 2 IEXP Indirect Exp。 a
4 NULL 1 INC收入b
5 1 2 MEXP其他进出口。 a
6 2 3 MCOST材料成本a
7 4 2 IINC Indirect Inc. b
8 6 4 TCOS瓷砖成本
如果您使用CTE触发以下查询，您将根据类别获得结果，该类别将根据级别进行组织。
CTE
（
从 tblhierarchy中选择 *
其中 parentid null 和 category = ' a'
union 所有
选择 a。* 来自 tblhierarchy as a inner join cte as b
on a.Parentid = b.id
其中 a.parentid 不 null < span class =code-keyword>和 a.category = ' a'
）
从 CTE中选择 *
这将为您提供以下输出。
ID ParentID Level代码名称类别
1 NULL 1 EXP费用
2 1 2 PEXP项目Exp。 a
3 1 2 IEXP Indirect Exp。 a
5 1 2 MEXP其他。进出口。 a
6 2 3 MCOST材料成本a
8 6 4 TCOS瓷砖成本a
现在输出等于你的产品除了订购代码。但就Parentid和水平而言，订单是正确的。因此，为了获得您期望的输出，您需要相应地进行ID编号或相应地订购。
另一条记录，即不同级别的记录可以如果你通过将类别更改为'b'来触发上述查询，就会看到。
希望它有所帮助，你已经理解了我试图描绘的内容。
I have a table like this in my SQL Server 2008.
ID ParentID Level Code Name Description
1 1 1 EXP Expenses --
2 1 2 PEXP Project Exp --
3 1 2 IEXP Indirect Exp. --
4 4 1 INC Incomes --
5 1 2 MEXP Misc. Exp. --
6 2 3 MCOST Material Cost --
7 4 2 IINC Indirect Inc. --
8 6 4 TCOS Tiles Cost --
I want a query which select all the rows in hierarchical order. (Tiles Cost under Material Cost, Material Cost under Project Expense, Project Expenses under Expenses etc. There can be at most 5 levels. There are 2000 rows in the table.)
Is this possible in SQL query?
The expected result should look like this:
ID ParentID Level Code Name Description
1 1 1 EXP Expenses --
3 1 2 IEXP Indirect Exp. --
5 1 2 MEXP Misc. Exp. --
2 1 2 PEXP Project Exp --
6 2 3 MCOST Material Cost --
8 6 4 TCOS Tiles Cost --
4 4 1 INC Incomes --
7 4 2 IINC Indirect Inc. --Hi....
As per the table structure, inorder to get the desired output, I think there is a need to change the table structure and the way records are organized.
Following are the changes:-
1) Keep the parentid as null for the records, which is equal to id i.e. Parentid equal to ID, in this case ID=1 (Expenses) and ID=4 (Incomes). If you have similar records in the future make sure those are null.
2)Since the Level changes or resets from 1 for every group of records, Create a colum for categorizing it or grouping it. For example in your scenario from Expenses to Tiles cost it is leveled from 1-4 and again the level is reset or different for records Incomes and Indirect. Therefore group/categorize it with a column and call it as 'A','B','C' ..... category.
So the table records with above changes looks as follows:-
ID ParentID Level Code Name Category 1 NULL 1 EXP Expenses a 2 1 2 PEXP Project Exp. a 3 1 2 IEXP Indirect Exp. a 4 NULL 1 INC Incomes b 5 1 2 MEXP Misc. Exp. a 6 2 3 MCOST Material Cost a 7 4 2 IINC Indirect Inc. b 8 6 4 TCOS Tiles Cost a
And if you fire the below query with the use of CTE you will get the result as per the category, which will be organized as per the level.
with CTE as ( Select * from tblhierarchy where parentid is null and category='a' union all Select a.* from tblhierarchy as a inner join cte as b on a.Parentid=b.id where a.parentid is not null and a.category='a' ) Select * from CTE
This will give you the following output.
ID ParentID Level Code Name Category 1 NULL 1 EXP Expenses a 2 1 2 PEXP Project Exp. a 3 1 2 IEXP Indirect Exp. a 5 1 2 MEXP Misc. Exp. a 6 2 3 MCOST Material Cost a 8 6 4 TCOS Tiles Cost a
Now in here the output is equal to yours except the ordering of Code. But with respect to Parentid and level the order is correct. So in order to get the output which you expect you need to make the ID numbering accordingly or order it accordingly.
The other piece of records i.e. different level of records can be seen if you fire the above query by changing the category to 'b'.
Hope it helps and you have understood what i tried to depict.
这篇关于查询对分层数据进行分组和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
