从sql表中获取计数

我有三个表,其中两个是主表,另一个是map.它们如下.

> tbl_Category,具有列Id(PK)和名称

/ *

ID      NAME
1   Agriculture & Furtilizers
2   Apparel & Garments
3   Arts & Crafts   
4   Automobiles

* /
> tbl_SubCategory

/*
Id      SubCategoryName                       CategoryId (FK,PK of above)
2   Badges,Emblems,Ribbons & Allied           2
3   Barcodes,Stickers & Labels                 2
4   Child Care & Nursery Products               2
9   Fabrics & Textiles                      2



*/

现在第三个表是tbl_Company_Category_Map,其中我持有公司的所有类别及其子类别.下面是它的架构和数据.

/*

CompanyCategoryId   SubCategoryId   CategoryId  CompanyId
10                   36             11          1
11                   38             11          1
12                   40             11          1


*/

上面,第一列是tbl_Company_Category_Map的PK,第二列是tbl_SubCategory的PK,第三列是tbl_Category的PK,最后一列是公司ID.
现在我想要的是显示一个类别的每个子类别中列出的显示总公司.有点像这样.

Subcategory Name                                        Total COmpanies 
Apparel,Clothing & Garments                             1153
Badges,Ribbons & Allied Products               4100
Barcodes,Stickers & Labels                              998
Child Care & Nursery Products                            2605
Cotton Bags,Canvas Bags,Jute Bags & Other Fabric Bags 2147

我正在使用查询：

BEGIN


SELECT     tbl_SubCategory.Name AS SubCategoryName,tbl_Category.Name AS CategoryName,TotalCompanies=(Select COUNT(*) From tbl_Company_Category_Map WHERE CategoryId = @Id)
FROM         tbl_Category INNER JOIN
                      tbl_Company_Category_Map ON tbl_Category.Id = tbl_Company_Category_Map.CategoryId INNER JOIN
                      tbl_SubCategory ON tbl_Company_Category_Map.SubCategoryId = tbl_SubCategory.Id
WHERE     (tbl_Company_Category_Map.CategoryId = @Id)
Group By tbl_SubCategory.Name,tbl_Company_Category_Map.CategoryId,tbl_Category.Name 
ORDER BY tbl_Company_Category_Map.CategoryId

END

我的问题是我每行的公司总数相同.请帮帮我.

解决方法

BEGIN


SELECT     tbl_SubCategory.Name AS SubCategoryName,COUNT(*) AS TotalCompanies
FROM       tbl_Category INNER JOIN
                      tbl_Company_Category_Map ON tbl_Category.Id = tbl_Company_Category_Map.CategoryId INNER JOIN
                      tbl_SubCategory ON tbl_Company_Category_Map.SubCategoryId = tbl_SubCategory.Id
WHERE     (tbl_Company_Category_Map.CategoryId = @Id)
Group By tbl_SubCategory.Name
ORDER BY tbl_SubCategory.Name

END

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!