3,491
社区成员
发帖
与我相关
我的任务
分享
with t as(
select '111111111111' id,to_date('2011-7-22 12:01','yyyy-MM-dd hh24:mi') date1 from dual
union all
select '111111111111' id,to_date('2011-7-22 12:06','yyyy-MM-dd hh24:mi') date1 from dual
union all
select '222222222222' id,to_date('2011-7-22 11:01','yyyy-MM-dd hh24:mi') date1 from dual
union all
select '222222222222' id,to_date('2011-7-22 12:03','yyyy-MM-dd hh24:mi') date1 from dual
union all
select '111111111111' id,to_date('2011-7-23 12:01','yyyy-MM-dd hh24:mi') date1 from dual
)
select tab.id,min(tab.date1)
from (
select t.id,t.date1,trunc(t.date1) tr
from t) tab
group by tab.id,tab.tr
SQL> WITH t AS (
2 SELECT '111111111111' phone,TO_DATE('2011-7-22 12:00:01','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
3 SELECT '111111111111' phone,TO_DATE('2011-7-22 12:00:02','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
4 SELECT '222222222222' phone,TO_DATE('2011-7-22 12:00:03','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
5 SELECT '222222222222' phone,TO_DATE('2011-7-22 12:00:04','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
6 SELECT '222222222222' phone,TO_DATE('2011-7-22 12:00:05','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
7 SELECT '111111111111' phone,TO_DATE('2011-7-23 12:00:06','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
8 SELECT '111111111111' phone,TO_DATE('2011-7-23 12:00:07','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
9 SELECT '111111111111' phone,TO_DATE('2011-7-23 12:00:08','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
10 SELECT '333333333333' phone,TO_DATE('2011-7-23 12:00:09','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
11 SELECT '333333333333' phone,TO_DATE('2011-7-23 12:00:10','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
12 SELECT '333333333333' phone,TO_DATE('2011-7-23 12:00:11','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
13 SELECT '222222222222' phone,TO_DATE('2011-7-23 12:00:12','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL UNION ALL
14 SELECT '222222222222' phone,TO_DATE('2011-7-23 12:00:13','yyyy-mm-dd hh24:mi:ss') tdate FROM DUAL
15 )
16 SELECT m.phone,
17 TO_CHAR(m.tdate,'yyyy-mm-dd hh24:mi:ss') tdate
18 FROM (SELECT t.phone,
19 t.tdate,
20 ROW_NUMBER() OVER(PARTITION BY t.phone, TO_CHAR(tdate, 'yyyymmdd') ORDER BY t.tdate DESC) rn
21 FROM t) m
22 WHERE m.rn = 1
23 ORDER BY m.tdate,
24 m.phone
25 ;
PHONE TDATE
------------ -------------------
111111111111 2011-07-22 12:00:02
222222222222 2011-07-22 12:00:05
111111111111 2011-07-23 12:00:08
333333333333 2011-07-23 12:00:11
222222222222 2011-07-23 12:00:13