SQL Server 计数来自父类别和所有子类别的记录 [英] SQL Server Count records from parent category and all subcategories

问题描述

目前我有一个存储过程，我在其中创建一个表并查询该表以获得我想要的结果，结果是一个无限分层的子/父表，允许我在基于 ASP Classic 的网页上显示数据.

这个程序是:

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

这个问题是底部的计数查询.

(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

任何帮助将不胜感激！谢谢

包括一些用于测试目的的 SQL

CREATE 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<32767)

这会为层次结构的每个级别提供一行.父类别出现多次，但具有不同的 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 @Categories 
SELECT CatID, CatName, ParentID = NULL FROM Categories
WHERE CatName NOT IN (
    SELECT CatName FROM Categories c 
    INNER JOIN CategoriesRel r ON c.CatID = r.ChildID)
UNION
SELECT CatID, CatName, cr.ParentID FROM Categories 
INNER JOIN CategoriesRel cr ON cr.ChildID = Categories.CatID
ORDER 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 CatCount
FROM 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 CatCount

If 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             | 1
1021  | Automotive           | 1
1024  | ---Repair            | 1
1026  | ------Engine Repair  | 1
1035  | ---Tyres             | 1
1002  | Building             | 0

I need the result to be something like this:

CatID | CatName              | CatCount
______|______________________|_________
1016  | Antiques             | 1
1021  | Automotive           | 4
1024  | ---Repair            | 2
1026  | ------Engine Repair  | 1
1035  | ---Tyres             | 1
1002  | Building             | 0

Any help would be greatly appreciated! Thanks

EDIT: Included is some SQL for testing purposes

CREATE 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) catCount
from r 
left outer join Businesses b on r.CatID = b.CatId
group 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) MaxDepth
from r
group by r.catId, CatName

The final query is:

SELECT y.CatID,
replicate('-',y.MaxDepth*3)+y.CatName CatName,
x.CatCount
FROM (select RootId, count(b.CatId) catCount
      from r 
      left outer join Businesses b on r.CatID = b.CatId
      group by rootid) x
join (select r.CatId, CatName, max(depth) MaxDepth
      from r
      group by r.catId, CatName) y on y.CatID = x.RootId
order by (select Sort from r where r.CatID = x.RootId and r.depth = y.MaxDepth)
OPTION(maxrecursion 32767);

这篇关于SQL Server 计数来自父类别和所有子类别的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！