#-------------------------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)