SELECT vhfloorno,
Count(ischeckin) AS ChecKIn,
Count(ischeckout) AS CheckOut
FROM visitorhistory t1
WHERE ( Year(vhcheckinebi) = Year(@From)
AND Month(vhcheckinebi) = Month(@From)
AND Day(vhcheckinebi) = Day(@From) )
OR ( Year(vhcheckoutebi) = Year(@To)
AND Month(vhcheckoutebi) = Month(@To)
AND Day(vhcheckoutebi) = Day(@To) )
GROUP BY vhfloorno
我想显示基于楼层的一张桌子的“签到和签出访问者”计数,但是通过此查询,签入和签出值是相同的。其中vhcheckinebi是访客签到的日期时间,而vhcheckoutebi是访客签出的日期时间。请帮我。
可能您需要条件聚合:
SELECT vhfloorno,
sum(case when ischeckin = 1 then 1 else 0 end) AS ChecKIn,
sum(case when ischeckout = 1 then 1 else 0 end) AS CheckOut
FROM visitorhistory t1
...
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句