试一下select ComputerName,SignTime, ScrStartTime from
(select * ,row_number() over ( Partition by ComputerName order by SignTime asc, ScrStartTime desc )rn from tb) t
where t.rn=1
group by ComputerName
(select * ,row_number() over ( Partition by ComputerName order by SignTime asc, ScrStartTime desc )rn from tb) t
where t.rn=1
group by ComputerName
(select * ,row_number() over ( Partition by ComputerName order by SignTime asc, ScrStartTime desc )rn from tb) t
where t.rn=1
group by ComputerName
-- starttime 取最小,endtime 最大,
-- 好象单行的没要,用一个 having count(*) > 1
select cp , min(starttime) , max(endtime)
from mytable
group by cp
having count(*) > 1
然后我想获得每台电脑的最早的scrstarttime和每台电脑的最晚的signtime.得到的表格如:
6-oqc2 2014-06-29 23:17:00 2014-07-06 05:53:00
acc00030 2014-07-04 18:56:00 2014-07-07 07:25:44
ACC0011 2014-07-02 12:04:00 2014-07-04 15:45:25