mySQL 查询以获取多个时间范围

Question

我目前使用 4 个查询来获取 4 个不同时间范围内的事件计数。

SELECT count(*) WHERE keyword='keyword' AND time>A AND time<B;
SELECT count(*) WHERE keyword='keyword' AND type>B AND time<C;
SELECT count(*) WHERE keyword='keyword' AND type>C AND time<D;
SELECT count(*) WHERE keyword='keyword' AND type>D AND time<E;

我认为必须有一种方法可以将它们合并到一个查询中。有什么想法吗？

Answer 1

更好的组合查询的方法

SELECT CASE
         WHEN time > A AND time < B THEN 'A-B'
         WHEN time > B AND time < C THEN 'B-C'
         WHEN time > C AND time < D THEN 'C-D'
         WHEN time > D AND time < E THEN 'D-E'
       END,
       Count(*)
FROM   yourtable
WHERE  keyword = 'keyword'
       AND time > A
       AND time < E
GROUP  BY CASE
            WHEN time > A AND time < B THEN 'A-B'
            WHEN time > B AND time < C THEN 'B-C'
            WHEN time > C AND time < D THEN 'C-D'
            WHEN time > D AND time < E THEN 'D-E'
          END