【SQL语句】MySql、SqlServer查询近期记录

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