以下是两张相关的表(简单的实例):
table1 (id, corpName) id代表公司编号,corpName代表公司名称 (两者都是候选键,前者主键)
table2 (personId, corpName) personId代表职员号(主键),corpName代表公司名称
现在想查找每个公司的详细信息以及公司职员数(id , corpName , pCount),而且假定公司的职员人数可以而且可能是0(即没有职员,pCount就是0),不知改如何用一句Select语句实现??
table1 (id, corpName) id代表公司编号,corpName代表公司名称 (两者都是候选键,前者主键)
table2 (personId, corpName) personId代表职员号(主键),corpName代表公司名称
现在想查找每个公司的详细信息以及公司职员数(id , corpName , pCount),而且假定公司的职员人数可以而且可能是0(即没有职员,pCount就是0),不知改如何用一句Select语句实现??
from table1 a left outer join table2 b
on a.corpname=b.corpname
from table1
FROM table1 a
LEFT JOIN
(SELECT corpName,COUNT(1) CNT FROM table2 GROUP BY corpName) b
ON b.corpName=a.corpName
from table1 a
left join (select corpname,sum(personid) as personid from table2 group by corpname)b on b.corpname = a.corpname
2,楼效的语句效率太差.哈哈
3,楼写的最好.建议楼主把分全给他
from table1
select a.corpName,count(personID) as Person
from table1 a left outer join table2 b
on a.corpname=b.corpname
group by a.corpName
因为使用count后,其他的字段必须使用group by
from table1 a
left join (select corpname,count(*) as personid from table2 group by corpname)b on b.corpname = a.corpname
insert into t1
select '三星'
union
select 'IBM'
union
select '微软'
union
select 'HP'create table t2(personId [int] IDENTITY (1, 1),[personName] varchar(10),[corpName] varchar(10))
insert into t2
select '小A','三星'
union all
select '小B','三星'
union all
select '小C','三星'
union all
select '小D','三星'
union all
select '小E','三星'
union all
select '小F','IBM'
union all
select '小G','IBM'
union all
select '小H','微软'
union all
select '小I','微软'
union all
select '小J','微软'
select
a.id,
a.corpName,
ISNULL(b.countnum,0) as countnum
from t1 a
left join
(select count(personId) as countnum,corpname from t2 group by corpname ) b
on a.corpname=b.corpname
select a.corpname,b.personid
from table1 a
left join (select corpname,count(*) as personid from table2 group by corpname)b on b.corpname = a.corpname
where a.cropName like 'a%'