日期:2014-05-18 浏览次数:20674 次
select t1.id,t1.work_id,t1.p_time,t1.p_name from (select n.* from csinfo_work m , csinfo_plun n where m.id = n.work_id and m.username = '张三' and datediff(day,n.p_time,getdate())<= 3) t1 where p_time = (select max(p_time) from (select n.* from csinfo_work m , csinfo_plun n where m.id = n.work_id and m.username = '张三' and datediff(day,n.p_time,getdate())<= 3) t2 where t2.work_id = t1.work_id) group by t1.id,t1.work_id,t1.p_time,t1.p_name
select distinct ta.id, ta.work_id,cast(ta.content as varchar(8000)) as content,ta.p_name,ta.p_time from csinfo_plun ta, (select a.* from csinfo_plun a,csinfo_work b where a.p_name=b.username) tb where ta.work_id=tb.work_id and ta.p_time>tb.p_time and datediff(day,tb.p_time,ta.p_time)<=3 /** id work_id content p_name p_time ----------- ----------- ---------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------ 3 1 睡了起来再吃点 王麻子 2009-02-28 00:22:34.000 4 1 睡了起来再吃点 王麻子 2009-02-28 00:23:34.000 8 3 睡了起来再吃点 王麻子 2009-02-28 00:27:34.000 (所影响的行数为 3 行) **/
------解决方案--------------------
如果把测试数据中的: insert into csinfo_plun(work_id,content,p_name,p_time)values(3,'大家都吃饱了','张三','2009-02-28 00:26:34') 改为 insert into csinfo_plun(work_id,content,p_name,p_time)values(3,'大家都吃饱了','李四','2009-02-28 00:26:34') 那么结果正确.
------解决方案--------------------