如何用sql实现？

Question

建表：
create table TEST1
(
  RECEIVETIME VARCHAR2(20),--接收时间
  DEVICEID    VARCHAR2(8), --设备号
  EMPLOYEEID  VARCHAR2(8)   --员工号
)
数据导入：
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 11:56:13', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 13:11:57', '00023151', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 14:33:26', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 15:54:00', '00023151', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 17:21:53', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-02 18:30:34', '00023151', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 11:54:32', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 13:11:49', '00023151', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 14:31:14', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 15:54:11', '00023151', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 17:10:24', '00023152', '00001389');
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID)
values ('2007-01-03 18:25:45', '00023151', '00001389');
commit;
查询：
select t.* from test1 t order by t.deviceid,t.receivetime
2007-01-02 13:11:57 00023151 00001389
2007-01-02 15:54:00 00023151 00001389
2007-01-02 18:30:34 00023151 00001389
2007-01-03 13:11:49 00023151 00001389
2007-01-03 15:54:11 00023151 00001389
2007-01-03 18:25:45 00023151 00001389
2007-01-02 11:56:13 00023152 00001389
2007-01-02 14:33:26 00023152 00001389
2007-01-02 17:21:53 00023152 00001389
2007-01-03 11:54:32 00023152 00001389
2007-01-03 14:31:14 00023152 00001389
2007-01-03 17:10:24 00023152 00001389要得到以下结果，sql怎么写？在线等
employeeid   deviceid   接收日期     第一次接收时间    第二次接收时间  第三次接收时间
00001389      00023151  2007-01-02        13:11:57         15:54:00     18:30:34
00001389      00023151  2007-01-03        13:11:49          15:54:11     18:25:45
.............

Answer 1

如果固定只显示三次，可以用DECODE+max实现

Answer 2

他是算每天的三次
select employeeid , deviceid ,to_char(RECEIVETIME,'yyyy-mm-dd')  接收日期 ,
       max(decode(rn,1,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第一次接收时间 ,
       max(decode(rn,2,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第二次接收时间 ,
       max(decode(rn,3,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第三次接收时间 from 
    (
     select a.* ,row_number() over (partition by EMPLOYEEID, DEVICEID ,trunc(RECEIVETIME) order by RECEIVETIME) rn
       from test1 a
    ) 
where rn<=3
group by employeeid , deviceid ,to_char(RECEIVETIME,'yyyy-mm-dd')

Answer 3

那你就取rn<=6
然后显示中固定显示6列
固定列的好做，用MAX+DECODE都可以实现，不固定列的就麻烦了select employeeid , deviceid ,to_char(RECEIVETIME,'yyyy-mm-dd')  接收日期 ,
       max(decode(rn,1,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第一次接收时间 ,
       max(decode(rn,2,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第二次接收时间 ,
       max(decode(rn,3,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第三次接收时间 
        max(decode(rn,4,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第四次接收时间 ,
       max(decode(rn,5,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第五次接收时间 ,
       max(decode(rn,6,to_char(RECEIVETIME,'hh24:mi:ss',null)) 第六次接收时间 from 
    (
     select a.* ,row_number() over (partition by EMPLOYEEID, DEVICEID ,trunc(RECEIVETIME) order by RECEIVETIME) rn
       from test1 a
    ) 
where rn<=6
group by employeeid , deviceid ,to_char(RECEIVETIME,'yyyy-mm-dd')

Answer 4

select employeeid,deviceid,
   max(decode(jj,1,substr(RECEIVETIME,12),'')) 第一次时间,
   max(decode(jj,2,substr(RECEIVETIME,12),'')) 第二次时间,
   max(decode(jj,3,substr(RECEIVETIME,12),'')) 第三次时间
from
(      
      select t.*,
        substr(RECEIVETIME,1,10) rq,
        row_number() over (partition by DEVICEID order by RECEIVETIME) jj
       from test1 t
)
group by  employeeid,deviceid;1 00001389 00023151 13:11:57 15:54:00 18:30:34
2 00001389 00023152 11:56:13 14:33:26 17:21:53

Answer 5

楼上的sql运行时，提示“invalid number”

Answer 6

RECEIVETIME VARCHAR2(20),--接收时间咋会是VARCHAR2呢？？？？？？？
如果是date型的，5楼的应该是可以实现的select employeeid,deviceid,
   rq 接受日期,
   max(decode(jj,1,substr(RECEIVETIME,12),'')) 第一次时间,
   max(decode(jj,2,substr(RECEIVETIME,12),'')) 第二次时间,
   max(decode(jj,3,substr(RECEIVETIME,12),'')) 第三次时间
from
(      
      select t.*,
        substr(RECEIVETIME,1,10) rq,
        row_number() over (partition by DEVICEID order by RECEIVETIME) jj
       from test1 t
)
group by  employeeid,deviceid;1 00001389 00023151 13:11:57 15:54:00 18:30:34
2 00001389 00023152 11:56:13 14:33:26 17:21:53

Answer 7

select deviceid,
       employeeid,
       to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') "DATE",
       max(decode(rn,1,to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'hh24:mi:ss')))"FIRST",
       max(decode(rn,2,to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'hh24:mi:ss')))"SECOND",
       max(decode(rn,3,to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'hh24:mi:ss'))) "THREE"
from
(
select deviceid,employeeid,RECEIVETIME,
row_number() over(partition by deviceid,employeeid,to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')  order by to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'hh24:mi:ss')) rn
From test1
) 
group by deviceid,
       employeeid,
       to_char(to_date(RECEIVETIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')

Answer 8

SELECT EMPLOYEEID,
       DEVICEID,rq 接收日期,
       MAX(DECODE(JJ, 1, SUBSTR(RECEIVETIME, 12), '')) 第一次时间,
       MAX(DECODE(JJ, 2, SUBSTR(RECEIVETIME, 12), '')) 第二次时间,
       MAX(DECODE(JJ, 3, SUBSTR(RECEIVETIME, 12), '')) 第三次时间,
       MAX(DECODE(JJ, 4, SUBSTR(RECEIVETIME, 12), '')) 第四次时间,
       MAX(DECODE(JJ, 5, SUBSTR(RECEIVETIME, 12), '')) 第五次时间,
       MAX(DECODE(JJ, 6, SUBSTR(RECEIVETIME, 12), '')) 第六次时间
  FROM (SELECT TEST1.*,
               SUBSTR(RECEIVETIME, 1, 10) RQ,
               ROW_NUMBER() OVER(PARTITION BY SUBSTR(RECEIVETIME, 1, 10),employeeid,DEVICEID ORDER BY RECEIVETIME) JJ
          FROM TEST1)
 GROUP BY rq, EMPLOYEEID, DEVICEID
 ORDER BY 1,2,3;
x

Answer 9

create table TEST1 
( 
  RECEIVETIME VARCHAR2(20),--接收时间 
  DEVICEID    VARCHAR2(8), --设备号 
  EMPLOYEEID  VARCHAR2(8)  --员工号 
) 
;
drop table test1;insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 11:56:13', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 13:11:57', '00023151', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 14:33:26', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 15:54:00', '00023151', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 17:21:53', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-02 18:30:34', '00023151', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 11:54:32', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 13:11:49', '00023151', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 14:31:14', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 15:54:11', '00023151', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 17:10:24', '00023152', '00001389'); 
insert into TEST1 (RECEIVETIME, DEVICEID, EMPLOYEEID) 
values ('2007-01-03 18:25:45', '00023151', '00001389'); select t.* from test1 t order by t.deviceid,t.receivetime ;select employeeid,deviceid,
   rq 接受时间,
   max(decode(jj,1,substr(RECEIVETIME,12),'')) 第一次接受时间,
   max(decode(jj,2,substr(RECEIVETIME,12),'')) 第二次接受时间,
   max(decode(jj,3,substr(RECEIVETIME,12),'')) 第三次接受时间
from
(      
      select t.*,
        substr(RECEIVETIME,1,10) rq,
        row_number() over (partition by employeeid,DEVICEID,substr(RECEIVETIME,1,10) order by RECEIVETIME) jj
       from test1 t
)
group by  employeeid,deviceid,rq;1 00001389 00023151 2007-01-02 13:11:57 15:54:00 18:30:34
2 00001389 00023151 2007-01-03 13:11:49 15:54:11 18:25:45
3 00001389 00023152 2007-01-02 11:56:13 14:33:26 17:21:53
4 00001389 00023152 2007-01-03 11:54:32 14:31:14 17:10:24

Answer 10

刚发现，时间居然是VARCHAR 

Answer 11

WITH a AS
(SELECT employeeid,deviceid,to_char(to_date(receivetime,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD')rq,to_char(to_date(receivetime,'yyyy-mm-dd hh24:mi:ss'),'hh24:mi:ssD')SJ from test1 ),
B AS
(SELECT
employeeid,deviceid,RQ,SJ,DENSE_RANK()OVER(PARTITION BY employeeid,deviceid,RQ ORDER BY SJ)XH
FROM
a
ORDER BY RQ)
SELECT
employeeid,deviceid,RQ,
MAX(DECODE(XH, 1, SJ, NULL))第一次,
MAX(DECODE(XH, 2, SJ, NULL))第二次,
MAX(DECODE(XH, 3, SJ, NULL))第三次
FROM
B
GROUP BY employeeid,deviceid,RQ
ORDER BY rq