mysql - SQL 基于时间戳差异计算ON Multi table with multi condition - MySQL

Question

表结构和示例数据

Wall_Update

   [INT         VARCHAR       VARCHAR         TIMESTAMP               TinyText]   
   pid [pk] |  CreatedBy  |   RepliedBy   |     Time               |     Text    

   1        |   sourav    |     sachin    |   2011-04-09 21:26:01  |      Hi

friend

  UID  |    fUID

sourav |   sachin  
sachin |   sourav

我需要获取所有由 SOURAV 或 SOURAV 的 friend 创建的帖子的 PID，并且时间应该至少是 3 天前的!

所以我尝试了这 2 个查询 [但都失败了]

select pid,`Text`,CreatedBy,Time,RepliedBy from WALL_UPDATE where CreatedBy in 
  (select FUID from friends where UID='sourav') or CreatedBy='sourav' and 
    TIMESTAMPDIFF (DAY , Time,CURRENT_TIMESTAMP)<=3 order by PID desc

SELECT WU.pid,WU.Text,MN.NameF,WU.Time,WU.CreatedBy FROM wall_update WU,main MN WHERE
  WU.CreatedBy in (select FUID from FRIENDS where UID='sourav') or
  WU.CreatedBy='sourav' and MN.uid=WU.createdBy and 
     TIMESTAMPDIFF (DAY , WU.time,CURRENT_TIMESTAMP ) <3 group by WU.pid

[两者都返回由 SOURAV 或 SOURAV 的 friend 创建的所有 PID]

Answer 1

试试这个

select * from WALL_UPDATE where (CreatedBy IN (select fuid from FRIENDS where uid ='sourav') or CreatedBy='sourav') and TIMESTAMPDIFF (DAY ,Time,now()) <=3
order by pid desc

使用主 id 来引用 friend 用户而不是名字，这将创建重复