SQL Server：在SQL查询中计算序列

Question

我在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
*/

以下是表格之间的关系：

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]

上述查询的输出结果为：

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

现在，我想要查找与父组1相关的所有子组和所有实体。另外，我想根据以下逻辑计算[ChildGroupSequence]和[EntitySequence]：

1. ChildGroupSequence列应表示父组中子组的顺序，从1000开始，每次增加100。即第一个子组将是1000，第二个子组将是1100。
2. EntitySequence列应表示子组内实体的顺序，从100开始，每次增加一个数字，为每个子组重置。即第一个子组中的第一个实体从100开始，第二个子组中的第一个实体也是如此。

因此，输出应该采用以下格式：

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

我可以通过将值读入应用程序层(.Net程序)来轻松完成此操作，但希望通过像这样尝试几件小事情来学习SQL Server。有人可以帮我编写这个SQL查询吗？

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

编辑：我的示例数据似乎没有正确反映第一个规则，规则指出ChildGroupSequence应该增加100，而示例输出增加了1。第二个查询反映了增加100的变化。@jpw: 非常感谢您指出这一点。

- Sathish

Answer 1

我相信可以使用分区和排名函数来完成此任务，代码如下：

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]

这个查询生成了您描述的示例输出。但是，您的示例数据似乎没有正确反映第一个规则，因为规则指出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]

请参考这个SQL Fiddle示例来了解两个查询的实际效果。

如果需要按照ID而不是名称进行分区，则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]

- jpw

---

非常感谢您的回答，非常感激。 - Sathish

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

结果：

- Jithin Shaji

---

非常感谢您的回答，非常感激。我之前错误地得出了样本输出，但是您的查询正好返回了我在样本输出中所描述的内容。 - Sathish

Answer 3

你可以通过使用排名函数来解决这个问题。

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]

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

- Aleksandr Fedorenko

---

非常感谢您的回答，我非常感激。我之前得到了错误的样本输出，但是您的查询确切地返回了我在样本输出中所示的内容。 - Sathish