主页 / dba / 问题 / 224846
Accepted
Nrj
Asked: 2018-12-13 21:15:46 +0800 CST

根据用户标识的特定条件仅选择那些行

  • 772

以下是儿童活动表的示例。我只想查看每个用户 ID 的 1st Tv 活动之后的所有行。我试过分组，但它对我不起作用

桌子

ID          Timestamp               Activity
567     1541300537817000            Games
567     1541300584959000            Tv
567     1541300711884001            Play
567     1541300749548000            Tv
567     1541300804865000            Cartoon
678     1542073550481000            Computer
678     1542073551422000            Tv
678     1542073551478000            Study
678     1542073561885000            Tv
678     1542073567663000            Sleep     
908     1543580126943000            Tv     
908     1543580212409000            Sleep     
908     1543580245227000            Play

预期产出 

567  Play  
567  Tv  
567  Cartoon    
678  Study  
678  Tv  
678  Sleep   
908  Sleep       
908  Play
mysql greatest-n-per-group

1 个回答

  1. Best Answer
    SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Tv';
    • 0

相关问题