sql选择连续时间段的值 [英] sql select values on Continuous period of time

问题描述

我有一个具有以下结构的表格:

I have a table with below structure:

id INT, order_id INT, datex DATE, timex TIME

例如，我想选择特定时间段内从 2017-10-24 到 2017-10-26 的订单 ID 计数.像 2:0:0 - 4:0:0,4:0:0 - 6:0:0 等，结果如下:

For example I want to select count of order ID's from 2017-10-24 to 2017-10-26 in specific periods of time. Like 2:0:0 - 4:0:0,4:0:0 - 6:0:0 etc and make a result like below:

period        | ordersID's
2:0:0 - 4:0:0 | 5
4:0:0 - 6:0:0 | 8

我可以仅通过查询来完成此操作，还是应该使用其他方式?

Can I do this by just a query or I should use other ways?

推荐答案

首先是您的时间段重叠.不清楚如果订单发生在 4 点，它们应该属于哪个时段.对于我下面的示例，我假设在时段结束时发生的订单属于下一个时段.

First thing is your time periods are overlapping. It is not clear that if orders occur at 4 what period they should belong in. For my examples below I am assuming orders occurring on the hour at the end of the period belong to the next period.

通过查询解决此问题的一种方法是在 SELECT 和 GROUP BY 子句中使用 case 语句.

One way to solve this with just a query is to use a case statement in your SELECT and GROUP BY clauses.

CREATE TABLE #test
(
    id int identity(1,1),
    order_id int,
    datex date,
    timex time
)

INSERT INTO #test VALUES
(5, '10/24/2017', '02:01'),
(5, '10/24/2017', '04:01'),
(6, '10/25/2017', '03:01'),
(7, '10/26/2017', '05:01'),
(8, '10/27/2017', '02:01')

SELECT
    CASE
        WHEN timex >= '02:00' AND timex < '04:00' THEN '2:0:0 - 4:0:0'
        WHEN timex >= '04:00' AND timex < '06:00' THEN '4:0:0 - 6:0:0'
        END AS 'periodLabel', 
    COUNT(order_id) AS 'OrderCount'
FROM
    #test
WHERE
    datex >= '10/24/2017'
    AND datex <= '10/26/2017'
GROUP BY
    CASE
        WHEN timex >= '02:00' AND timex < '04:00' THEN '2:0:0 - 4:0:0'
        WHEN timex >= '04:00' AND timex < '06:00' THEN '4:0:0 - 6:0:0'
        END

解决此问题的另一种方法是创建一个包含您的时间段的表并将其加入.我更喜欢这个解决方案，因为它更容易维护.

Another way to solve this is to create a table with your time periods in it and join it. I prefer this solution because it is easier to maintain.

CREATE TABLE #timePeriod
(
    periodLabel varchar(50),
    periodBegin time,
    periodEnd time
)

INSERT INTO #timePeriod VALUES
('02:00 - 04:00', '02:00', '04:00'),
('04:00 - 06:00', '04:00', '06:00')

SELECT
    B.periodLabel,
    COUNT(order_id) AS 'OrderCount'
FROM
    #test AS A
    JOIN #timePeriod AS B
        ON A.timex >= B.periodBegin
            AND A.timex < B.periodEnd
WHERE
    A.datex >= '10/24/2017'
    AND A.datex <= '10/26/2017'
GROUP BY
    B.periodLabel

这篇关于sql选择连续时间段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！