我在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]:
因此,输出应采用以下格式:
-------------------------------------------------------------------------------
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:非常感谢指出这一点。
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]
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句