比如有两个查询,查同一个student表:第一个查询:
select class,count(*) count1 from student where age<20 group by class
返回:
class,count1第二个查询:
select class,count(*) count2 from student where age>22 group by class
返回:
class,count2现在我想用一个查询得到下面的结果,应该怎么写?查询:
select ???
返回
class,count1,count2
select class,count(*) count1 from student where age<20 group by class
返回:
class,count1第二个查询:
select class,count(*) count2 from student where age>22 group by class
返回:
class,count2现在我想用一个查询得到下面的结果,应该怎么写?查询:
select ???
返回
class,count1,count2
select class,
SUM(Case When age<20 Then 1 Else 0 End) As count1,
SUM(Case When age>22 Then 1 Else 0 End) As count2
from student
group by class
select class,count(*) count1,0 from student where age<20 group by class
union all
select class,count1=0,count(*) count2 from student where age>22 group by class
) group by class
或用
UNION ALL把两条语句联合起来
在关键字 'group' 附近有语法错误。最后一个group过不去
select class,count1=sum(count1),count2=sum(count2) from (
select class,count(*) count1,0 from student where age<20 group by class
union all
select class,count1=0,count(*) count2 from student where age>22 group by class
) as 表别名 group by class