SQL获取两列

Question

我在 Oracle 10g 数据库中有类似的记录

`with t as (
   select 1234 emp_number, to_date('19-JAN-09 07.06.00 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 08:40:53 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 08:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 09:50:32 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 10:07:18 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN'  SPECIFIER from dual union all
   select 1234 emp_number, to_date('19-JAN-09 02:49:07 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all   
   select 1234 emp_number, to_date('22-JAN-09 10:42:50 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('23-JAN-09 06:50:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('23-JAN-09 10:49:55 PM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:22:19 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:30:46 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:43:51 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:48:40 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 05:50:52 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 06:03:02 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'IN' SPECIFIER from dual union all
   select 1234 emp_number, to_date('24-JAN-09 06:43:44 AM', 'dd-MON-yy hh.mi.ss AM') TIME_IN_OUT, 'OUT' SPECIFIER from dual
   )`

如果您查看 2009 年 1 月 22 日的数据，我们会发现 IN_TIME 为 2009 年 1 月 22 日 10.42.50 PM，第二天 OUT_TIME 为 2009 年 1 月 23 日 06.50.40 AM 等等同一天 IN_TIME 为 2009 年 1 月 23 日 10.49.55 PM，OUT_TIME 为 2009 年 1 月 24 日 05:22:19 AM。因此，理想的输出应该是

EMP_NUMBER DAY                TIME_IN                                           TIME_OUT
`
1234         19/Jun/11          19-JAN-09 10.07.18 AM               19-JAN-09 10.50.32 AM
1234         22/Jun/11          22-JAN-09 10.42.50 PM                   NIL
1234         23/Jun/11                  NIL                          23-JAN-09 06.50.40 AM
1234         24/Jun/11          23-JAN-09 10.49.55 PM                   NIL
1234         24/Jun/11                  NIL                       24-JAN-09 05:22:19 AM  
`

如果一天有多个时钟，我怎样才能获得 deired 输出并显示所有时钟？

Answer 1

一个简单的强力解决方案是：

SELECT a.emp_number, a.time_in_out as time_in, (SELECT
   MIN(b.time_in_out)
   FROM yourtable b
   WHERE b.specifier='OUT'
   AND b.emp_number=a.emp_number
   AND b.time_in_out>=a.time_in_out) as time_out
FROM yourtable a
WHERE a.specifier='IN'
ORDER BY a.time_in_out;

但是，如果您想选取没有输入/输出记录的日期，则需要对完整的日期表进行外连接，或者使用 PL/SQL 来迭代序列。

Answer 2

根据 @symcbean 答案尝试此操作 -
下面的查询将给出您想要的输出..
如果您有任何疑问或需要任何澄清，请告诉我（（添加您的评论））

SELECT * FROM  
       (
         SELECT emp_number,
                to_char(time_out ,'DD-MM-YYYY') day,
                decode(to_char(time_in ,'DD-MM-YYYY'),
                       to_char(time_out ,'DD-MM-YYYY'),
                       min(time_in),
                       null
                       ) time_in,
                max(time_out) time_out
         FROM (
                SELECT a.emp_number,
                a.time_in_out as time_in,
                (
                   SELECT MIN(b.time_in_out)
                   FROM t b
                   WHERE b.specifier = 'OUT'
                     AND b.emp_number = a.emp_number
                     AND b.time_in_out >= a.time_in_out
                 ) as time_out
                 FROM t a
                 WHERE a.specifier = 'IN'
                )
                group by to_char(time_in ,'DD-MM-YYYY'),to_char(time_out ,'DD-MM-YYYY'),emp_number
          union all 
          SELECT  emp_number,to_char(time_out ,'DD-MM-YYYY'),
                  max(time_in),
                  null time_out
           FROM (
                  SELECT a.emp_number,
                         a.time_in_out as time_in,
                         (
                            SELECT MIN(b.time_in_out)
                            FROM t b
                            WHERE b.specifier = 'OUT'
                            AND b.emp_number = a.emp_number
                            AND b.time_in_out >= a.time_in_out
                          ) as time_out
                          FROM t a
                          WHERE a.specifier = 'IN'
                   )
                   group by to_char(time_in ,'DD-MM-YYYY'),to_char(time_out ,'DD-MM-YYYY'),emp_number
   HAVING to_char(time_in ,'DD-MM-YYYY')<> to_char(time_out ,'DD-MM-YYYY')
   )
   ORDER BY nvl(TIME_IN,time_out)