如何选择它们按日期

问题描述：

分组这是MyTable的数据基础上连续3行的分钟（时间）：如何选择它们按日期

select Name, LastName, Id, LocationId, Date, Time, RN from mytable 


Name LastName Id LocationId Date  Time  RN 
Office Jones  284 NY   20171004 1540  1 
Office Jones  284 NY   20171004 1545  2 
Office Jones  284 NY   20171004 1550  3 

Office Jones  284 NY   20171004 1650  4 

Office Jones  284 NY   20171113 1030  1 
Office Jones  284 NY   20171113 1035  2 
Office Jones  284 NY   20171113 1040  3 

Office Jones  284 NY   20171113 1130  4 
Office Jones  284 NY   20171113 1135  5 
Office Jones  284 NY   20171113 1140  6 

Office Jones  284 NY   20171113 1230  7 
Office Jones  284 NY   20171113 1435  8 
Office Jones  284 NY   20171113 1740  9 

Office Jones  284 NY   20171113 1840  10 

Office Jones  284 NY   20171115 1030  1 
Office Jones  284 NY   20171115 1035  2 
Office Jones  284 NY   20171115 1040  3

我想展示我的结果作为名称，名字，身份证， LocationId，日期，在此基础上按日期分组连续3个排分钟（时间）

Name LastName Id LocationId Date  Time 
Office Jones  284 NY   20171004 1540 
Office Jones  284 NY   20171113 1030 
Office Jones  284 NY   20171113 1130 
Office Jones  284 NY   20171113 1230 
Office Jones  284 NY   20171115 1030

这是我迄今为止，但我无法弄清楚如何得到其他2行到我结果

SELECT Name, LastName, Id, LocationId, Date, min(Time) as Time FROM mytable 
WHERE Date IN (SELECT Date 
         FROM mytable 
         WHERE RN = 3) 
GROUP BY Name, LastName, Id, LocationId, Date 



Name LastName Id LocationId Date  Time 
Office Jones  284 NY   20171004 1540 
Office Jones  284 NY   20171113 1030 
Office Jones  284 NY   20171115 1030

是不是你的WHERE RN = 1'的结果数据？ –

答

如何使用算术rn？

select name, lastname, id, locationid, date, min(time) 
from t 
group by name, lastname, id, locationid, 
     (rn - 1)/3;

或者，只需where：

select name, lastname, id, locationid, date, time 
from t 
where rn % 3 = 1;

答

这里是一个简化版本删除常数值列，只专注于计算所需的列（我会倾入临时表）。内部联接中的派生表确定最大行数，每个日期是三的倍数，并且由三个结果集群在外部查询的分组上：

select #tmp.dates, 
     min(#tmp.times) 
from #tmp 
inner join (select dates, 
        max(rnum) rnum_x 
      from #tmp 
      where rnum % 3 = 0 
      group by dates) x 
    on #tmp.dates = x.dates 
    and #tmp.rnum <= x.rnum_x 
group by #tmp.dates, 
     case when rnum%3 = 0 
       then rnum/3 - 1 
     else rnum/3 
     end

如何选择它们按日期

相关推荐