mysql - SQL 按日期集分组

Question

好的，我有一个表，其中包含以下列:名称、c_id、日期。

该表中的某些条目遵循特定的条件，我可以使用 WHERE 有效地选择这些条件，因此这不是问题。

那么，除了 name 和 c_id 列之外，我想要的是一种对三个日期进行分组的方法:date - 1、date 和 date + 1。换句话说，我希望输出的每一行代表与某个相关条目具有相同名称和 c_id 的所有条目，并且这些条目发生在该条目的前一天和后一天之间，包括其本身。

我该如何去做呢？

--编辑:

(EDIT2:原始表应该是 Table1 和 Table2 ON Table1.id = Table2.id 的 INNER JOIN)

示例数据:

Table1:
  id  |  c_id  |     date     |  other stuff
-----------------------------------------------------
  01  |  abc   |  2015/12/09  |  whatever
  02  |  abc   |  2015/12/09  |  whatever
  03  |  abc   |  2015/12/10  |  relevant criterion
  04  |  abc   |  2015/12/11  |  whatever
  05  |  def   |  2015/11/15  |  whatever
  06  |  def   |  2015/11/16  |  relevant criterion
  07  |  abc   |  2015/11/17  |  whatever
  08  |  mnc   |  2016/01/02  |  whatever
  09  |  mnc   |  2016/01/02  |  whatever
  10  |  mnc   |  2016/01/03  |  whatever
  11  |  mnc   |  2016/01/03  |  whatever
  12  |  mnc   |  2016/01/03  |  whatever
  13  |  mnc   |  2016/01/04  |  relevant criterion
  14  |  mnc   |  2016/01/05  |  whatever
  15  |  mnc   |  2016/01/05  |  whatever
  16  |  mnc   |  2016/01/06  |  whatever

Table2:
  id  |  Name  |  other stuff
--------------------------------------
  01  |  John  |  whatever
  02  |  John  |  whatever
  03  |  John  |  whatever
  04  |  John  |  whatever
  05  |  Mary  |  whatever
  06  |  Mary  |  whatever
  07  |  Mary  |  whatever
  08  |  Alice |  whatever
  09  |  Alice |  whatever
  10  |  Alice |  whatever
  11  |  Alice |  whatever
  12  |  Alice |  whatever
  13  |  Alice |  whatever
  14  |  Alice |  whatever
  15  |  Alice |  whatever
  16  |  Alice |  whatever

所需输出示例:

  Name  |  c_id  |  pivot_date  |  count
------------------------------------------
  John  |  abc   |  2015/12/10  |  4
  Mary  |  def   |  2015/11/16  |  2
  Alice |  mnc   |  2016/01/04  |  6

(pivot_date部分不一定是符合相关标准的部分，涉及的任何一个日期都可以。)

Answer 1

更新了新的示例数据:

  SELECT t.name, t.c_id, t.date pivot_date, COUNT(*) count
    FROM record t
    JOIN record t2
      ON t2.name = t.name
     AND t2.c_id = t.c_id
     AND t2.date >= t.date - INTERVAL 1 DAY        
     AND t2.date <= t.date + INTERVAL 1 DAY
   WHERE t.other_stuff = 'relevant criterion'
GROUP BY t.name, t.c_id, t.date

See SQLFiddle

--

更新了新的示例数据:

  SELECT t2.name, t1.c_id, t1.date pivot_date, COUNT(*) count
    FROM table1 t1
    JOIN table1 to1
      ON to1.c_id = t1.c_id
     AND to1.date >= t1.date - INTERVAL 1 DAY        
     AND to1.date <= t1.date + INTERVAL 1 DAY
    JOIN table2 
      ON t2.id = t1.id
   WHERE t1.other_stuff = 'relevant criterion'
GROUP BY t2.name, t1.c_id, t1.date