SQL：按列的日期和范围获取数据

I have a MySQL table called 'Library' below with three columns. I need to get count of range of dwell_time for each date. The ranges I need is 0-30 , 31-60, 61-90 and 91-100.

我有一个名为'Library'的MySQL表,下面有三列。我需要计算每个日期的停留时间范围。我需要的范围是0-30,31-60,61-90和91-100。

    Id   Date        dwell_time 
    1  2015-05-10      27
    2  2015-05-10      28
    3  2015-05-10      32
    4  2015-05-10      65
    5  2015-05-11      27
    6  2015-05-11      28
    7  2015-05-11      65

should return

2015-05-10 0-30 2
2015-05-10 31-60 1
2015-05-10 61-90 1
2015-05-11 0-30 2
2015-05-11 61-90 1

Can you please help with the query I need to use?

你能帮我解决一下我需要使用的查询吗?

1 个解决方案

#1

SELECT
    date,
    date_range,
    count(1)
FROM (
    SELECT
        date,
        CASE
            WHEN dwell_time BETWEEN 0 AND 30 THEN '0-30'
            WHEN dwell_time BETWEEN 31 AND 60 THEN '31-60'
            WHEN dwell_time BETWEEN 61 AND 90 THEN '61-90'
            ELSE '90-100'
        END AS date_range
    FROM Library) lib
GROUP BY date, date_range

EDIT (by Gordon, because I was asked in the comments):

编辑(戈登,因为我在评论中被问到):

    SELECT date,
           (CASE WHEN dwell_time BETWEEN 0 AND 30 THEN '0-30'
                 WHEN dwell_time BETWEEN 31 AND 60 THEN '31-60'
                 WHEN dwell_time BETWEEN 61 AND 90 THEN '61-90'
                 ELSE '90-100'
            END) AS date_range,
           COUNT(*)
    FROM Library l
    GROUP BY date, date_range
    ORDER BY date, date_range;

Here is the SQL Fiddle.

这是SQL小提琴。

#1

SELECT
    date,
    date_range,
    count(1)
FROM (
    SELECT
        date,
        CASE
            WHEN dwell_time BETWEEN 0 AND 30 THEN '0-30'
            WHEN dwell_time BETWEEN 31 AND 60 THEN '31-60'
            WHEN dwell_time BETWEEN 61 AND 90 THEN '61-90'
            ELSE '90-100'
        END AS date_range
    FROM Library) lib
GROUP BY date, date_range