mysql - 从父值计算子成员

Question

我有一个类似conversations 和conversation_timelines 的表格

conversations 表示例

|  id   |   last_active   |   category_id   |
|-------------------------------------------|
|  1    |   1552462134    |        1        |
|  2    |   1552461332    |        1        |
|  3    |   1552462312    |        2        |
|  4    |   1552461772    |        1        |

conversation_timelines 表示例

|  id   |  conversation_id  |  message  |  created_time  |
|--------------------------------------------------------|
|  1    |        1          |  hi       |  1552462066    |
|  2    |        1          |  hello    |  1552462172    |
|  3    |        1          |  world    |  1552462188    |
|  4    |        2          |  another  |  1552462141    |

我要查询的是统计 conversation_timelines that has created_time > conversation's last_active 中的记录数WHERE conversations.category_id = 1

结果世界是这样的

|  conversation_id  |  count  |
|         1         |    2    |
|         2         |    1    |

Answer 1

使用 join 和 group by 如下所示

  SELECT ct.conversation_id,count(ct.message)
  FROM conversation_timelines ct
  INNER JOIN  conversation c on c.id=ct.conversation_id and c.category_id=1
  and ct.created_time>c.last_active
  GROUP BY ct.conversation_id