有两个表
表1: product
pid pName cid
1 p1 1
2 p2 4
3 p3 2
4 p4 3
5 p5 1表2:company
cid cName cLocation
1 c1 sh
2 c2 bj
3 c3 jn
4 c4 sz
2 c2 qd
3 c3 cd
1 c1 tj表1三个字段分别是产品id,产品名称,公司id(通过此字段连接表company)
表2三个字段分别是公司id,公司名称,公司位置我写了如下sql统计生产某一产品的公司数目
select company.cName,count(company.cid ) as Count from
product join company on company.cid = product.cid where product.pid='1' group by company.cName
结果:
cName Count
c1 2
问题是表2:company中存放了分公司的信息(cLocation不同),比如
1 c1 sh
1 c1 tj
不统计分公司,比如pid=1属于公司c1就只统计一次,我想要的结果是
cName Count
c1 1如何写sql?
表1: product
pid pName cid
1 p1 1
2 p2 4
3 p3 2
4 p4 3
5 p5 1表2:company
cid cName cLocation
1 c1 sh
2 c2 bj
3 c3 jn
4 c4 sz
2 c2 qd
3 c3 cd
1 c1 tj表1三个字段分别是产品id,产品名称,公司id(通过此字段连接表company)
表2三个字段分别是公司id,公司名称,公司位置我写了如下sql统计生产某一产品的公司数目
select company.cName,count(company.cid ) as Count from
product join company on company.cid = product.cid where product.pid='1' group by company.cName
结果:
cName Count
c1 2
问题是表2:company中存放了分公司的信息(cLocation不同),比如
1 c1 sh
1 c1 tj
不统计分公司,比如pid=1属于公司c1就只统计一次,我想要的结果是
cName Count
c1 1如何写sql?
cName Count
c1 1
product join (
select distinct cid,cName from company
)company on company.cid = product.cid where product.pid='1' group by company.cName
if object_id('product') is not null drop table product
create table product(pid int, pName varchar(10),cid int)
insert product
select 1, 'p1', 1
union all select 2, 'p2', 4
union all select 3, 'p3', 2
union all select 4, 'p4', 3
union all select 5, 'p5', 1 if object_id('company') is not null drop table company
create table company(cid int,cName varchar(10), cLocation varchar(10))
insert company
select 1, 'c1', 'sh'
union all select 2, 'c2', 'bj'
union all select 3, 'c3', 'jn'
union all select 4, 'c4', 'sz'
union all select 2, 'c2', 'qd'
union all select 3, 'c3', 'cd'
union all select 1, 'c1', 'tj' select company.cName,count(distinct(company.cid)) as Count --加DISTINCT去重复
from product join company
on company.cid = product.cid and product.pid='1'
group by company.cName
/*
cName Count
c1 1
*/