在SQL Server中，一组为两个平均值

Question

我有两个group-by和average查询，我需要把这两个结果放在一起。我的意思是假设我有两个这样的查询：

select timespan, count(*) c1
from dbo 
where C > 50
group by timespan
having count(*) > 100

和

select timespan, count(*) c2
from dbo 
where C < 50
group by timespan
having count(*) > 130

已编辑我需要将两者放在一起

TimeSpan, c1, c2
630,  120, 100
631,  140, 120

这是我在SQL Server中的查询，但它不起作用：

select 
gt, ls 
from  
(select count (*) gt 
from dbo.Alltrips 
where DrivingTime > 50  
and CarId in (select CarId from dbo.Alltrips 
group by CarId 
having Count (*) > 100) 
group by timeSpan) a,
(select count (*) ls 
from dbo.Alltrips 
where DrivingTime < 50  
and CarId in (select CarId from dbo.Alltrips 
group by CarId 
having Count (*) > 130) 
group by timeSpan) b

Answer 1

使用条件聚合：

SELECT timespan,
COUNT(CASE WHEN C > 100 THEN 1 END) AS C1,
COUNT(CASE WHEN C > 130 THEN 1 END) AS C2
FROM dbo.dbo --Do you really have a table called dbo?
GROUP BY timespan
HAVING COUNT(CASE WHEN C > 100 THEN 1 END) > 100;