SQL Server 计数来自父类别和所有子类别的记录

这个程序是:SET NOCOUNT ON;声明@类别表(CatID INT 不为空，CatName VARCHAR(200) 非空，家长 ID INT)插入@Categories从类别中选择 CatID、CatName、ParentID = NULL猫名不在 (SELECT CatName FROM Categories cINNER JOIN CategoriesRel r ON c.CatID = r.ChildID)联盟从类别中选择 CatID、CatName、cr.ParentIDINNER JOIN CategoriesRel cr ON cr.ChildID = Categories.CatID按 CatID 订购；与 r AS (SELECT CatID, CatName, ParentID, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX))从 @Categories 哪里 ParentID 为 NULL联合所有SELECT c.CatID, c.CatName, c.ParentID, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200))FROM r INNER JOIN @Categories c ON r.CatID=c.ParentID WHERE r.Depth这个问题是底部的计数查询.(SELECT COUNT(BsnID) FROM Businesses WHERE Businesses.CatID = r.CatID) AS CatCount如果我使用这段特定的代码，我只会得到为特定类别 ID 返回的行数.例如，这是当前的结果:CatID |猫名 |猫数______|______________________|_________1016 |古董 |11021 |汽车 |11024 |---维修|11026 |------发动机维修|1第1035章---轮胎 |11002 |建筑 |0我需要这样的结果:CatID |猫名 |猫数______|______________________|_________1016 |古董 |11021 |汽车 |41024 |---维修|21026 |------发动机维修|1第1035章---轮胎 |11002 |建筑 |0任何帮助将不胜感激！谢谢包括一些用于测试目的的 SQLCREATE TABLE Categories(CatID int NOT NULL,CatName nvarchar(100) NOT NULL,PRIMARY KEY (CatID));CREATE TABLE CategoriesRel(CatLinkID int NOT NULL,ParentID int NOT NULL,ChildID int NOT NULL,PRIMARY KEY (CatLinkID),FOREIGN KEY (ParentID) REFERENCES Categories(CatID),FOREIGN KEY (ChildID) REFERENCES Categories(CatID);CREATE TABLE Businesses(BsnID int NOT NULL,BsnName nvarchar(100) NOT NULL,CatID int NOT NULL,PRIMARY KEY (BsnID),FOREIGN KEY (CatID) REFERENCES Categories(CatID);INSERT INTO Categories VALUES ('1','Antique'),('2','Automotive'),('3','Building'),('4','Tyres'),('5','修理'),('6','发动机修理');INSERT INTO CategoriesRel VALUES ('1', '2','4'),('1','2','5'),('1','5','6');INSERT INTO Businesses VALUES ('1','Test1','2'),('2','Test2','4'),('3','Test3','5'),('4','Test4','6'); 解决方案 去掉 CTE 的 WHERE ParentID IS NULL 部分，并添加一个 RootId 字段.这将让您找到每个父级的子级数.;with r AS (SELECT CatID, CatName, ParentID, CatID RootId, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX))来自@Categories--父ID为空的地方联合所有SELECT c.CatID, c.CatName, c.ParentID, RootId, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200))从 r内部连接@Categories c ON r.CatID=c.ParentIDWHERE r.Depth这会为层次结构的每个级别提供一行.父类别出现多次，但具有不同的 RootId.这将使我们在层次结构的每个级别获得计数:+-------+---------------+----------+--------+-------+----------------------------------------------+|目录号 |猫名 |家长 ID |根 ID |深度|排序 |+-------+---------------+---------+--------+-------+--------------------------------+|1002 |建筑 |空 |1002 |0 |建筑 ||1016 |古董 |空 |1016 |0 |古董 ||1021 |汽车 |空 |1021 |0 |汽车 ||1024 |维修 |1021 |1024 |0 |维修 ||1026 |发动机维修 |1024 |1026 |0 |发动机维修 ||第1035章轮胎 |1021 |第1035章0 |轮胎 ||1026 |发动机维修 |1024 |1024 |1 |维修引擎维修 ||1024 |维修 |1021 |1021 |1 |汽车维修 ||第1035章轮胎 |1021 |1021 |1 |汽车轮胎 ||1026 |发动机维修 |1024 |1021 |2 |AutomotiveRepairEngine Repair |+-------+---------------+---------+--------+-------+--------------------------------+如果您按 RootId 分组并获得 COUNT()，它会为您提供您要查找的数字:select RootId, count(b.CatId) catCount从 rr.CatID = b.CatId 上的左外连接业务 b按rootid分组+--------+-----------+|根 ID |猫数 |+--------+-----------+|1002 |0 ||1016 |1 ||1021 |4 ||1024 |2 ||1026 |1 ||第1035章1 |+--------+-----------+查询的其余部分只是获取排序和缩进的 CatName.你想得到每个类别最深的孩子:select r.CatId, CatName, max(depth) MaxDepth从 r按 r.catId、CatName 分组最终查询是:SELECT y.CatID,replica('-',y.MaxDepth*3)+y.CatName CatName,x.CatCountFROM (选择 RootId, count(b.CatId) catCount从 rr.CatID = b.CatId 上的左外连接业务 b按rootid分组)x加入 (选择 r.CatId, CatName, max(depth) MaxDepth从 rgroup by r.catId, CatName) y on y.CatID = x.RootId排序依据(选择从 r 排序，其中 r.CatID = x.RootId 和 r.depth = y.MaxDepth)选项(最大递归 32767)；Currently I have a stored procedure where I create a table and query the table to get my desired result, the result being an infinitely tiered child/parent table that allows me to display the data on my ASP Classic based webpage.This procedure is:SET NOCOUNT ON;DECLARE @Categories TABLE( CatID INT NOT NULL, CatName VARCHAR(200) NOT NULL, ParentID INT)INSERT INTO @CategoriesSELECT CatID, CatName, ParentID = NULL FROM CategoriesWHERE CatName NOT IN ( SELECT CatName FROM Categories c INNER JOIN CategoriesRel r ON c.CatID = r.ChildID)UNIONSELECT CatID, CatName, cr.ParentID FROM CategoriesINNER JOIN CategoriesRel cr ON cr.ChildID = Categories.CatIDORDER BY CatID;WITH r AS ( SELECT CatID, CatName, ParentID, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX)) FROM @Categories WHERE ParentID IS NULL UNION ALL SELECT c.CatID, c.CatName, c.ParentID, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200)) FROM r INNER JOIN @Categories c ON r.CatID=c.ParentID WHERE r.Depth<32767)SELECT CatID, CatName=replicate('-',r.Depth*3)+r.CatName,(SELECT COUNT(BsnID) FROM Businesses WHERE Businesses.CatID = r.CatID) AS CatCountFROM r ORDER BY Sort OPTION(maxrecursion 32767);The problem with this is the count query at the bottom.(SELECT COUNT(BsnID) FROM Businesses WHERE Businesses.CatID = r.CatID) AS CatCountIf I use this specific piece of code, I only get the count of rows returned for a specific Category ID. For example this is the current result:CatID | CatName | CatCount______|______________________|_________1016 | Antiques | 11021 | Automotive | 11024 | ---Repair | 11026 | ------Engine Repair | 11035 | ---Tyres | 11002 | Building | 0I need the result to be something like this: CatID | CatName | CatCount______|______________________|_________1016 | Antiques | 11021 | Automotive | 41024 | ---Repair | 21026 | ------Engine Repair | 11035 | ---Tyres | 11002 | Building | 0Any help would be greatly appreciated! ThanksEDIT: Included is some SQL for testing purposesCREATE TABLE Categories(CatID int NOT NULL,CatName nvarchar(100) NOT NULL,PRIMARY KEY (CatID));CREATE TABLE CategoriesRel(CatLinkID int NOT NULL,ParentID int NOT NULL,ChildID int NOT NULL,PRIMARY KEY (CatLinkID),FOREIGN KEY (ParentID) REFERENCES Categories(CatID),FOREIGN KEY (ChildID) REFERENCES Categories(CatID);CREATE TABLE Businesses(BsnID int NOT NULL,BsnName nvarchar(100) NOT NULL,CatID int NOT NULL,PRIMARY KEY (BsnID),FOREIGN KEY (CatID) REFERENCES Categories(CatID);INSERT INTO Categories VALUES ('1','Antique'),('2','Automotive'),('3','Building'),('4','Tyres'),('5','Repair'),('6','Engine Repairs');INSERT INTO CategoriesRel VALUES ('1', '2','4'),('1','2','5'),('1','5','6');INSERT INTO Businesses VALUES ('1','Test1','2'),('2','Test2','4'),('3','Test3','5'),('4','Test4','6'); 解决方案 Take out the WHERE ParentID IS NULL part of the CTE, and add a RootId field. This will let you find the count of the children for each level of parent.;WITH r AS ( SELECT CatID, CatName, ParentID, CatID RootId, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX)) FROM @Categories --WHERE ParentID IS NULL UNION ALL SELECT c.CatID, c.CatName, c.ParentID, RootId, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200)) FROM r INNER JOIN @Categories c ON r.CatID=c.ParentID WHERE r.Depth<32767)This gives you a row for each level of the hierarchy. Parent categories appear multiple times, but with different RootIds. This will let us get a count at each level of the hierarcy:+-------+---------------+----------+--------+-------+-------------------------------+| CatID | CatName | ParentID | RootId | depth | Sort |+-------+---------------+----------+--------+-------+-------------------------------+| 1002 | Building | NULL | 1002 | 0 | Building || 1016 | Antiques | NULL | 1016 | 0 | Antiques || 1021 | Automotive | NULL | 1021 | 0 | Automotive || 1024 | Repair | 1021 | 1024 | 0 | Repair || 1026 | Engine Repair | 1024 | 1026 | 0 | Engine Repair || 1035 | Tyres | 1021 | 1035 | 0 | Tyres || 1026 | Engine Repair | 1024 | 1024 | 1 | RepairEngine Repair || 1024 | Repair | 1021 | 1021 | 1 | AutomotiveRepair || 1035 | Tyres | 1021 | 1021 | 1 | AutomotiveTyres || 1026 | Engine Repair | 1024 | 1021 | 2 | AutomotiveRepairEngine Repair |+-------+---------------+----------+--------+-------+-------------------------------+If you group by RootId and get the COUNT(), it gives you the numbers you're looking for:select RootId, count(b.CatId) catCountfrom rleft outer join Businesses b on r.CatID = b.CatIdgroup by rootid+--------+----------+| RootId | CatCount |+--------+----------+| 1002 | 0 || 1016 | 1 || 1021 | 4 || 1024 | 2 || 1026 | 1 || 1035 | 1 |+--------+----------+The rest of the query is just getting the Sort and indented CatName. You want to get the deepest child for each category:select r.CatId, CatName, max(depth) MaxDepthfrom rgroup by r.catId, CatNameThe final query is:SELECT y.CatID,replicate('-',y.MaxDepth*3)+y.CatName CatName,x.CatCountFROM (select RootId, count(b.CatId) catCount from r left outer join Businesses b on r.CatID = b.CatId group by rootid) xjoin (select r.CatId, CatName, max(depth) MaxDepth from r group by r.catId, CatName) y on y.CatID = x.RootIdorder by (select Sort from r where r.CatID = x.RootId and r.depth = y.MaxDepth)OPTION(maxrecursion 32767); 这篇关于SQL Server 计数来自父类别和所有子类别的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..