表 project
projectID, projectName, Others表 team
teamID, teamName, projectID我希望用一句话显示出 project的列表,以及每个project包含team的个数,比如这样的最终效果:项目名称,项目备注,小组数
xxx xxx 3先谢过!!!!
projectID, projectName, Others表 team
teamID, teamName, projectID我希望用一句话显示出 project的列表,以及每个project包含team的个数,比如这样的最终效果:项目名称,项目备注,小组数
xxx xxx 3先谢过!!!!
from project a
left join team b on a.projectid=b.projectid
A.projectName As 项目名称,
A.Others As 项目备注,
IsNull(Count(B.teamID),0) As 小组数
From project A
Left Join team B
On A.projectID=B.projectID
Group By A.projectName,A.Others
select a.projectName as 项目名称,a.Others as 项目备注,count(b.teamid) as 小组数
from project a
left join team b on a.projectid=b.projectid
(select count(*) from team where projectID=a.projectID) as 小组数
from project a
select a.projectName as 项目名称,a.Others as 项目备注,count(b.teamid) as 小组数
from project a
left join team b on a.projectid=b.projectid
group by a.projectName,a.Others orselect a.projectName as 项目名称,a.Others as 项目备注,count(b.teamName) as 小组数
from project a
left join team b on a.projectid=b.projectid
group by a.projectName,a.Others
from project a, team b
where b.projectid = a.projectid
group by a.projectName, a.Others
declare @Item table (ItemId int , ItemName varchar(100) , proid int )
insert into @pro
select '1','1#项目','1#项目Other'
union all
select '2','2#项目','2#项目Other'insert into @Item
select '1','1小组','1'
union all
select '2','2小组','1'
union all
select '3','3小组' ,'2'select a.* , (select count(*) from @Item b where b.proid = a.proid ) as 参加小组
from @pro a
from project a,team b where a.projectid=b.projectid