select b.ID, b.Name, count(1) PC from Category a, SubCategory b, Product c where a.ID=b.CatID and b.ID=c.SubCatID and a.Name='??' group by b.ID, b.Name
select b.ID, b.Name, count(1) PC from Category a join SubCategory b on a.ID=b.CatID left join Product c on b.ID=c.SubCatID where a.Name='??' group by b.ID, b.Name
Select TP.SubCatID, TS.Name SubCatName, Count(TP.ID) As ProductCount From SubCategory TS, Product TP Where TS.CatID = TP.SubCatID
Select TP.SubCatID, TS.Name SubCatName, Count(TP.ID) As ProductCount From SubCategory TS, Product TP Where TS.CatID = TP.SubCatID Group By TP.SubCatID, TS.Name SubCatName
create table Category(id int, name varchar(20)) insert Category select 1,'厨房间' union select 2,'卫浴间' create table SubCategory(id int,name varchar(20),catid int) insert SubCategory select 1,'橱柜',1 union all select 2,'水槽及厨龙',1 union all select 3,'卫浴洁具',2 union all select 4,'墙地砖',2 create table Product(id int,name varchar(20),catid int,subcatid int) insert Product select 1,'产品1', 1, 1 union all select 2,'产品2', 1, 1 union all select 3,'产品3', 1, 2 union all select 4,'产品4', 2, 1 union all select 5,'产品5', 2, 3 union all select 6,'产品6', 1, 3 union all select 7,'产品7', 1, 4 union all select 8,'产品8', 2, 1 go select SubCatID=a.id,SubCatName=a.name,ProductCount=COUNT(c.name) from SubCategory a full join Category b on a.catid=b.id full join Product c on a.id=c.subcatid and b.id=c.catid group by a.id,a.name order by a.id /* SubCatID SubCatName ProductCount NULL NULL 4 1 橱柜 2 2 水槽及厨龙 1 3 卫浴洁具 1 4 墙地砖 0 */ go drop table Category,subcategory,product
from Category a, SubCategory b, Product c
where a.ID=b.CatID and b.ID=c.SubCatID and a.Name='??'
group by b.ID, b.Name
刚刚调试了一下您所提供的Sql code,显示的结果集是正确的。
比如:大类下的某一子类没有商品记录,应该是显示0条记录,以上sql code却不能实现。
select b.ID, b.Name, count(1) PC
from Category a join SubCategory b on a.ID=b.CatID
left join Product c on b.ID=c.SubCatID
where a.Name='??'
group by b.ID, b.Name
Select TP.SubCatID, TS.Name SubCatName, Count(TP.ID) As ProductCount
From SubCategory TS, Product TP
Where TS.CatID = TP.SubCatID
Select TP.SubCatID, TS.Name SubCatName, Count(TP.ID) As ProductCount
From SubCategory TS, Product TP
Where TS.CatID = TP.SubCatID
Group By TP.SubCatID, TS.Name SubCatName
insert Category
select 1,'厨房间' union
select 2,'卫浴间'
create table SubCategory(id int,name varchar(20),catid int)
insert SubCategory
select 1,'橱柜',1 union all
select 2,'水槽及厨龙',1 union all
select 3,'卫浴洁具',2 union all
select 4,'墙地砖',2
create table Product(id int,name varchar(20),catid int,subcatid int)
insert Product
select 1,'产品1', 1, 1 union all
select 2,'产品2', 1, 1 union all
select 3,'产品3', 1, 2 union all
select 4,'产品4', 2, 1 union all
select 5,'产品5', 2, 3 union all
select 6,'产品6', 1, 3 union all
select 7,'产品7', 1, 4 union all
select 8,'产品8', 2, 1
go
select SubCatID=a.id,SubCatName=a.name,ProductCount=COUNT(c.name) from SubCategory a
full join Category b on a.catid=b.id
full join Product c on a.id=c.subcatid and b.id=c.catid
group by a.id,a.name
order by a.id
/*
SubCatID SubCatName ProductCount
NULL NULL 4
1 橱柜 2
2 水槽及厨龙 1
3 卫浴洁具 1
4 墙地砖 0
*/
go
drop table Category,subcategory,product