#-------------------------MYSQL------------------------- #每小时记录 SELECT HOUR(open_time) hourNum, COUNT(1) hourCount FROM b_entrance_guard_record GROUP BY HOUR(open_time) #近六个月出入记录 SELECT MONTH(n.open_time) monthNum, COUNT(1) monthCount FROM ( SELECT * FROM b_entrance_guard_record WHERE open_time > DATE_SUB(NOW(), INTERVAL 6 MONTH) AND turnover_type = 0 ) n GROUP BY YEAR(n.open_time),MONTH(n.open_time) #近十天记录 SELECT MONTH(n.open_time) monthNum, DAY(n.open_time) dayNum, COUNT(1) COUNT FROM ( SELECT * FROM b_entrance_guard_record WHERE DATE_SUB(CURDATE(), INTERVAL 9 DAY) <= DATE(open_time) ) n GROUP BY YEAR(n.open_time), MONTH(n.open_time), DAY(n.open_time) #上月总访客量 SELECT COUNT(1) FROM ( SELECT * FROM b_entrance_guard_record WHERE PERIOD_DIFF(DATE_FORMAT(NOW() , '%Y%m') , DATE_FORMAT(open_time, '%Y%m')) = 1 ) n GROUP BY YEAR(n.open_time), MONTH(n.open_time) #本月访客量 SELECT COUNT(1) 总人数 FROM ( SELECT * FROM b_entrance_guard_record WHERE DATE_FORMAT(open_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) ) n GROUP BY YEAR(n.open_time), MONTH(n.open_time) #近十小时出入记录 SELECT HOUR(n.open_time) hourNum, COUNT(1) hourCount FROM ( SELECT * FROM b_entrance_guard_record WHERE open_time > DATE_SUB(NOW(), INTERVAL 10 HOUR) AND turnover_type = 1 ) n GROUP BY YEAR(n.open_time), MONTH(n.open_time), DAY(open_time), HOUR(open_time) #近十小时总记录 SELECT COUNT(1) FROM b_entrance_guard_record WHERE open_time > DATE_SUB(NOW(), INTERVAL 10 HOUR) AND turnover_type = 1
#-------------------------SQLSERVER------------------------- #查询当天记录 SELECT SUM(Cash_Amount) onLineAmount, SUM(OnLine_Amount) cashAmount FROM Statistics_Gather WHERE ParkingID = '5fb93f53-c127-4a49-924e-a79500c5790d' AND YEAR(GatherTime) = YEAR(GETDATE()) AND MONTH(GatherTime) = MONTH(GETDATE()) AND DAY(GatherTime) = DAY(GETDATE()) #查询近十天的出入记录 SELECT SUM(Exit_Count) exitCount , SUM(Entrance_Count) entranceCount, DAY(GatherTime) DAY FROM Statistics_Gather WHERE DATEDIFF(dd,GatherTime,getdate()) <= 10 AND ParkingID = #{PKid} GROUP BY DAY(GatherTime) ,MONTH(GatherTime)
