SQL sorting group hierarchy Filtering-God's party.

Objective:

There have been 2 kinds of lists, 1 are sorted, there are filtering functions, but there is no hierarchy and grouping, 2 is a tree structure of the hierarchy and grouping, but the data is all loaded out, although a little behind, no use to page loading, but also subject to the large environment.

Today there are 1 requirements that are grouped by sorting the filter hierarchy, and at first did not think of a good idea, and later learned that you could order by group sort.

Core idea:

There are 2 fields in the table, hierarchy level, ID, ParentID, this is the first filter of the data ID and parentid form 1 sets, in the original data table to find the corresponding data in the collection, and then do 2 more fields (this demand is such a sort, If you need to be able to adjust yourself, one is the groupid,1 of the partition is the sort Groupinnnerid within the grouping, in order to prevent the parent-child confusion after click Sorting, the ID in this group is always ascending, or always descending.

Code reference:

SELECT     --group ID, dividing the parent-child element into the same 1 region     Case  whenParent is NULL  ThenReportIDELSEParentEND  asGroupID,--the parent in the zone does not participate in the sort, so it is set to 0 and the other children are 1     Case  whenParent is NULL  Then 0 ELSE 1 END  asGroupinnerid,--contains 1 fields indicating the first level of the line, used to make a distinction between color, bold, etc.    *  fromdbo. ReportlistWHEREReportIDinch (    --ID of the data that was supposed to be    SELECTReportID fromdbo. ReportlistWHEREActiveRecord= 1  andUrl is  not NULL  andReportName like '%proj%'    UNION  All    --The parent ID of the above data    SELECTParent fromdbo. ReportlistWHEREActiveRecord= 1  andUrl is  not NULL  andReportName like '%proj%')--1. By region grouping, ascending descending and the fields that need to be sorted remain the same--2. The parent-child sort in the group is always sorted in ascending order, because the parent node should be sorted on the 1th record anyway--3. Personalized sorting--4. It is recommended to add 1 non-repeating fields at the end to prevent the order from being sorted in the first 3 cases, so that clicking on the sort order will be sorted out.ORDER  byGroupID, Groupinnerid, ReportNameDESC

The result of the above 1 practices is not ideal, that is, multiple columns need to be sorted in case it is not perfect.

Given that C # is simpler and easier to write than SQL in terms of processing loops, here's a 2nd way of Thinking :

There are 2 levels, the target data is first detected, and then the parent of the level=1 and level=2 found to do 1 to go to the weight, using union, and then according to their own needs, then the level=2 into another table, and then sequentially traverse the 1th table of each row, In the 2nd table to find its child nodes, in order to add, then the completion of the DataTable is already out of order, and there is a hierarchy, the advantage is not to want to splice hierarchy field, the disadvantage is not flexible to deal with multilevel problems.

If you have a better and more flexible ideas, you can give me a message, knowledge needs to share is precious!