子查询计数SQL

Question

我有这段代码，我需要取消行计数。我试图计数，但结果是无法对包含聚合或子查询的表达式执行聚合函数。

你能帮我数排吗，谢谢。

select count((
select a.GIRIS_ZAMANI, a.CIKIS_ZAMANI, a.PLAKA, a.UCRET, b.KAMERA_ADI
from ARAC_CIKIS a 
left join KAMERALAR b 
on b.KAMERA_ID = a.CIKIS_KAMERA_ID
where a.CIKIS_ZAMANI between 
(select cast(cast(sysutcdatetime() as date) as datetime) + cast('00:00:00' as datetime)) and 
(select cast(cast(sysutcdatetime() as date) as datetime) + cast('23:59:59' as datetime)) and 
a.UCRET>0
)
)

Answer 1

您可以在下面尝试-

select count(*)
from
(
select a.GIRIS_ZAMANI, a.CIKIS_ZAMANI, a.PLAKA, a.UCRET, b.KAMERA_ADI  from ARAC_CIKIS a left join KAMERALAR b on b.KAMERA_ID = a.CIKIS_KAMERA_ID
where a.CIKIS_ZAMANI between cast(cast(sysutcdatetime() as date) as datetime) 
+ cast('00:00:00' as datetime) and cast(cast(sysutcdatetime() as date) as datetime) 
+ cast('23:59:59' as datetime)
and  a.UCRET>0
) A

Answer 2

为什么要使用子查询？

select count(*)
from ARAC_CIKIS a left join
KAMERALAR k
on bkKAMERA_ID = a.CIKIS_KAMERA_ID
where a.CIKIS_ZAMANI between cast(cast(sysutcdatetime() as date) as datetime) + cast('00:00:00' as datetime) and
cast(cast(sysutcdatetime() as date) as datetime) + cast('23:59:59' as datetime) and
a.UCRET > 0