mysql - SQL : Get data by date and range of a column

Question

我在下面有一个名为“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

应该返回

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

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

Answer 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

编辑(由 Gordon 撰写，因为我在评论中被问到):

    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是 SQL fiddle 。