我有三个表,其中两个是主表,另一个是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
