mysql - SQL查询自动输入日期变量

Question

我正在使用 SQLYog 上的计划报告功能来发送我们生产线的每小时生产报告。我遇到的问题是如何让我的查询自动填充开始日期和结束日期。以下是我的查询示例:

我基本上只需要自动填充第一个变量中的日期戳，因为类次开始和停止时间始终相同。

SET

@DayStart = '2013-11-06 07:00:00',
@DayEnd = '2013-11-06 16:00:00';

SELECT Product1.rec_cnt AS 'Handset',
        CONCAT(Product1EOL1.rec_cnt, ' / ', Product1EOL2.rec_cnt) AS 'Handset EOL',
        Product2.rec_cnt AS 'Wallet',
        Product3.rec_cnt AS 'Wholesale',
        CONCAT(Product3EOL1.rec_cnt, ' / ', Product3EOL2.rec_cnt) AS 'Wholesale EOL',
        Product4.rec_cnt AS 'Blister',
        CONCAT(Product4EOL1.rec_cnt, ' / ', Product4EOL2.rec_cnt) AS 'Blister EOL'
FROM
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '3'
    GROUP BY StartHour, EndHour
) AS Product1
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '10'
    AND status_id = '4'
    GROUP BY StartHour, EndHour
) AS Product1EOL1
ON Product1.StartHour = Product1EOL1.StartHour AND Product1.EndHour = Product1EOL1.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '10'
    AND status_id = '6'
    GROUP BY StartHour, EndHour
) AS Product1EOL2
ON Product1.StartHour = Product1EOL2.StartHour AND Product1.EndHour = Product1EOL2.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '2'
    GROUP BY StartHour, EndHour
) AS Product2
ON Product1.StartHour = Product2.StartHour AND Product1.EndHour = Product2.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '6'
    GROUP BY StartHour, EndHour
) AS Product3
ON Product1.StartHour = Product3.StartHour AND Product1.EndHour = Product3.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '9'
    AND status_id = '4'
    GROUP BY StartHour, EndHour
) AS Product3EOL1
ON Product1.StartHour = Product3EOL1.StartHour AND Product1.EndHour = Product3EOL1.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '9'
    AND status_id = '6'
    GROUP BY StartHour, EndHour
) AS Product3EOL2
ON Product1.StartHour = Product3EOL2.StartHour AND Product1.EndHour = Product3EOL2.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '4'
    GROUP BY StartHour, EndHour
) AS Product4
ON Product4.StartHour = Product3.StartHour AND Product4.EndHour = Product3.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id)  AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '8'
    AND status_id = '4'
    GROUP BY StartHour, EndHour
) AS Product4EOL1
ON Product1.StartHour = Product4EOL1.StartHour AND Product1.EndHour = Product4EOL1.EndHour
INNER JOIN
(
    SELECT StartHour, EndHour, COUNT(t_container.container_id) AS rec_cnt
    FROM 
    (
        SELECT DATE_ADD(@DayStart, INTERVAL i HOUR) AS StartHour, DATE_ADD(DATE_ADD(@DayStart, INTERVAL i HOUR), INTERVAL 3599 SECOND) AS EndHour
        FROM
        (
            SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
        ) Sub1
    ) Sub2
    LEFT OUTER JOIN t_container
    ON created_timestamp BETWEEN StartHour AND EndHour
    AND container_type_id = '8'
    AND status_id = '6'
    GROUP BY StartHour, EndHour
) AS Product4EOL2
ON Product1.StartHour = Product4EOL2.StartHour AND Product1.EndHour = Product4EOL2.EndHour

Answer 1

您使用 CURDATE() 查询今天并连接小时字符串以获得您想要的时间:

SET @DayStart = concat(curdate(), ' 07:00:00'),
    @DayEnd   = concat(curdate(), ' 16:00:00');