求有点复杂的COUNT语句

[jacbey]

来源表：
MANid TEAM AGE
1 A 21
2 A 22
3 B 19
4 B 23
5 B 22
6 C 18

输出：
TEAM cntMAN minAGE
A 2 21
B 2 22
C 0 null

要求：cntMAN＝每个TEAM有多少AGE>20的记录，minAGE＝min(AGE)

[wangdehao]

select a.TEAM,a.cntMAN,b.minAGE from (select TEAM,count(*) as cntMAN from table group by TEAM)a left join (select TEAM,minAGE＝min(AGE) from table where AGE>20 group by TEAM)b
on a.TEAM = b.TEAM

[jixiaojie]

SELECT TEAM, COUNT(*) AS cntMAN, MIN(AGE) AS minAGE
FROM tb
WHERE (AGE > 20)
GROUP BY TEAM

UNION ALL

SELECT TEAM, '0', NULL
FROM tb
WHERE (AGE < 20) AND TEAM NOT IN (SELECT TEAM
FROM tb
WHERE (AGE > 20))
GROUP BY TEAM

[paoluo]

Select A.TEAM,IsNull(B.cntMAN,0) As cntMAN,B.minAGE
from (Select Distinct TEAM from TableName) A
Left Join (Select TEAM,Count(1) As cntMAN,Min(AGE) As minAGE from TableName Where AGE>20 Group By TEAM )B
On A.TEAM=B.TEAM
Order By A.TEAM

[paoluo]

--建立测试环境
Create Table TEST
(MANid Int Identity(1,1),
TEAM Varchar(10),
AGE Int)
--插入数据
Insert TEST Values('A', 21)
Insert TEST Values('A', 22)
Insert TEST Values('B', 19)
Insert TEST Values('B', 23)
Insert TEST Values('B', 22)
Insert TEST Values('C', 18)
--测试
Select A.TEAM,IsNull(B.cntMAN,0) As cntMAN,B.minAGE
from (Select Distinct TEAM from TEST) A
Left Join (Select TEAM,Count(1) As cntMAN,Min(AGE) As minAGE from TEST Where AGE>20 Group By TEAM )B
On A.TEAM=B.TEAM
Order By A.TEAM
--删除测试环境
Drop Table TEST
--结果
/*
TEAM cntMAN minAGE
A 2 21
B 2 22
C 0 NULL
*/

[talantlee]

select team, cntMAN=(select count(distinct team) from #tmp where age>20),Minage=min(age) from #tmp group by team

[paoluo]

楼上的语句就知道是错的，没仔细看明白楼主的意思。