从 sql 表中获取计数 [英] Getting count from sql tables

问题描述

我有三个表，其中两个是主表，另一个是地图.它们在下面给出.

I have three tables in which two are master tables and other one is map. They are given below.

1. tbl_Category，包含 Id (PK) 和 Name 列

1. tbl_Category, having columns Id (PK) and Name

/*

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

*/

tbl_SubCategory

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，我在其中保存了一家公司的所有类别及其子类别.下面是它的架构和数据.

Now the third table is tbl_Company_Category_Map, where I am holding all categories and its subcategories of a company. below is its schema and data.

/*

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.现在我想要的是显示一个类别的每个子类别中列出的显示公司总数.像这样.

Above, first column is the PK of tbl_Company_Category_Map, second column is PK of tbl_SubCategory and third one is PK of tbl_Category and last one is the company id. Now what i want is to display the display total companies listed in each subcategory of a category. Somethinglike this.

Subcategory Name                                        Total COmpanies 
Apparel, Clothing & Garments                             1153
Badges, Emblems, 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

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

My Problem is that I am getting total number of companies same for each row. Please help me.

推荐答案

试试这个:

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

这篇关于从 sql 表中获取计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！