SQL server：计算SQL查询中的序列

I have the following four tables in SQL Server 2008R2:

我在SQL Server 2008R2中有以下四个表:

DECLARE @ParentGroup TABLE (ParentGroup_ID INT, ParentGroup_Name VARCHAR(100));
DECLARE @ChildGroup TABLE (ChildGroup_id INT, ChildGroup_name VARCHAR(100), ParentGroup_id INT);
DECLARE @Entity TABLE ([Entity_id] INT, [Entity_name] VARCHAR(100));
DECLARE @ChildGroupEntity TABLE (ChildGroupEntity_id INT, ChildGroup_id INT, [Entity_ID] INT);
INSERT INTO @parentGroup VALUES (1, 'England'), (2, 'USA');
INSERT INTO @ChildGroup VALUES (10, 'Sussex', 1), (11, 'Essex', 1), (12, 'Middlesex', 1);
INSERT INTO @entity VALUES (100, 'Entity0'),(101, 'Entity1'),(102, 'Entity2'),(103, 'Entity3'),(104, 'Entity4'),(105, 'Entity5'),(106, 'Entity6');
INSERT INTO @ChildGroupEntity VALUES (1000, 10, 100), (1001, 10, 101), (1002, 10, 102), (1003, 11, 103), (1004, 11, 104), (1005, 12, 100), (1006, 12, 105), (1007, 12, 106);
/*
SELECT * FROM @parentGroup
SELECT * FROM @ChildGroup
SELECT * FROm @entity
SELECT * FROM @ChildGroupEntity
*/

The relationships between the tables as below:

表格之间的关系如下:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name],  0 [ChildGroupSequence], 0 [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

The output of the above query is:

上述查询的输出是:

-------------------------------------------------------------------------------
ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence|
-------------------------------------------------------------------------------
England         |Essex          |Entity3    |0                 |0             |
England         |Essex          |Entity4    |0                 |0             |
England         |Middlesex      |Entity0    |0                 |0             |
England         |Middlesex      |Entity5    |0                 |0             |
England         |Middlesex      |Entity6    |0                 |0             |
England         |Sussex         |Entity0    |0                 |0             |
England         |Sussex         |Entity1    |0                 |0             |
England         |Sussex         |Entity2    |0                 |0             |
-------------------------------------------------------------------------------

Now, I want to find out the child groups and all entities associated with the child groups for parent group 1. Also, I want to calculate the [ChildGroupSequence], [EntitySequence] as for the logic below:

现在,我想找出子组和与父组1的子组关联的所有实体。另外,我想计算[ChildGroupSequence],[EntitySequence],如下面的逻辑:

1. The ChildGroupSequence column should represent the child group’s sequence within the parent group, starting from 1000 and incrementing by 100. Ie first subgroup will be 1000, second subgroup will be 1100.

ChildGroupSequence列应表示子组在父组中的序列,从1000开始并递增100.即,第一个子组将为1000,第二个子组将为1100。

2. The EntitySequence column should represent the entity sequence within the child group, starting from 100 and incrementing by single digits, resetting for each subgroup. I.e. the first entity in childgroup 1 starts at 100, as does the first entity in childgroup 2.

EntitySequence列应表示子组内的实体序列,从100开始并按单个位递增,为每个子组重置。即子组1中的第一个实体从100开始,子组2中的第一个实体也是如此。

So, the output should be in the following format:

因此,输出应采用以下格式:

-------------------------------------------------------------------------------
ParentGroup_Name|ChildGroup_name|Entity_name|ChildGroupSequence|EntitySequence|
-------------------------------------------------------------------------------
England         |Essex          |Entity3    |1000              |100           |
England         |Essex          |Entity4    |1000              |101           |
England         |Middlesex      |Entity0    |1100              |100           |
England         |Middlesex      |Entity5    |1100              |101           |
England         |Middlesex      |Entity6    |1100              |102           |
England         |Sussex         |Entity0    |1200              |100           |
England         |Sussex         |Entity1    |1200              |101           |
England         |Sussex         |Entity2    |1200              |102           |
-------------------------------------------------------------------------------

I can do this easily by reading values into the application layer (.Net program), but want to learn SQL server by experimenting a few little things like this. Could anyone help me in writing this SQL query?

我可以通过在应用程序层(.Net程序)中读取值来轻松完成此操作,但希望通过尝试这样的一些小事来学习SQL服务器。任何人都可以帮我写这个SQL查询吗?

Any help would be much appreciated. Thanks in advance.

任何帮助将非常感激。提前致谢。

EDIT: My sample data hadn't seem to correctly reflect the first rule, the rule states that ChildGroupSequence should be incremented by 100 and the sample output increments by 1. The second query reflects the increment by 100. @jpw: Thank you very much for pointing this out.

编辑:我的样本数据似乎没有正确反映第一条规则,规则规定ChildGroupSequence应增加100,样本输出增加1.第二个查询反映增量为100. @jpw:非常感谢指出这个。

3 个解决方案

#1

I believe this can be accomplished using partitioning and ranking functions like this:

我相信这可以使用这样的分区和排名函数来完成:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    999 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

This query generates the sample output you described. Your sample data does not seem to correctly reflect the first rule though as the rule states that ChildGroupSequence should be incremented by 100 and the sample output increments by 1. The second query reflects the increment by 100:

此查询生成您描述的示例输出。您的示例数据似乎没有正确反映第一个规则,但规则规定ChildGroupSequence应增加100并且示例输出增加1.第二个查询将增量反映100:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    900 + 100 * DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY ChildGroup_name, Entity_name) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

Please see this sample SQL Fiddle for examples of both queries.

有关这两个查询的示例,请参阅此示例SQL Fiddle。

Maybe the query should partition by ID and not Name, if so Sussex will come before Essex as it has a lower ID and the query would be this:

也许查询应该按ID而不是Name进行分区,如果是这样,Sussex会在Essex之前进行分区,因为它具有较低的ID并且查询将是:

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name], 
    900 + 100 * DENSE_RANK() OVER(PARTITION BY pg.ParentGroup_ID ORDER BY cg.ChildGroup_ID) AS [ChildGroupSequence], 
    99 + ROW_NUMBER() OVER(PARTITION BY pg.ParentGroup_ID, cg.ChildGroup_ID ORDER BY cg.ChildGroup_ID, cge.Entity_ID) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY pg.ParentGroup_ID, cg.ChildGroup_ID, [Entity_name]

#2

SELECT  ParentGroup_Name, 
        ChildGroup_name,
        [Entity_name],  
        LU.R [ChildGroupSequence], 
        99 + ROW_NUMBER() OVER (PARTITION BY LU.ParentGroup_id,LU.ChildGroup_id ORDER BY ChildGroup_name) [EntitySequence]
FROM    @ChildGroupEntity cge
JOIN    (
        SELECT  cg.ChildGroup_id,   
                cg.ChildGroup_name, 
                pg.ParentGroup_id,  
                pg.ParentGroup_Name,    
                999 + (ROW_NUMBER() OVER (ORDER BY cg.ChildGroup_id)) [R]
        FROM    @ChildGroup cg 
        JOIN    @parentGroup pg On pg.ParentGroup_ID = cg.ParentGroup_ID) LU
        ON      cge.ChildGroup_id = LU.ChildGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY LU.ParentGroup_Name, LU.ChildGroup_name, e.[Entity_name]

Result:

#3

You can solve this by using ranking functions

您可以使用排名功能解决此问题

SELECT ParentGroup_Name, ChildGroup_name, [Entity_name],
       899 + DENSE_RANK() OVER(PARTITION BY ParentGroup_Name ORDER BY ChildGroup_name) +
       100 * DENSE_RANK() OVER(ORDER BY ParentGroup_Name ASC) AS ChildGroupSequence,
       99 + ROW_NUMBER() OVER(PARTITION BY ParentGroup_Name, ChildGroup_name ORDER BY [Entity_name]) AS [EntitySequence]
FROM @ChildGroupEntity cge
INNER JOIN @ChildGroup cg ON cg.ChildGroup_id=cge.ChildGroup_id
INNER JOIN @parentGroup pg ON pg.parentGroup_id=cg.parentGroup_id
INNER JOIN @entity e ON e.[entity_id]=cge.[Entity_ID]
ORDER BY ParentGroup_Name, ChildGroup_name, [Entity_name]

Result you can find here SQL Fiddle

结果你可以在这里找到SQL Fiddle