select a.user_name, a.area, a.time, b.area, b.time from test a, test bwhere a.user_name = b.user_name and a.type = '签到' and a.user_name = '张三' and To_char(a.time, 'yyyy-MM-dd') = To_char(b.time, 'yyyy-MM-dd');就行了
SELECT a.user_name, a.area, a.time, b.area, b.time FROM (SELECT user_name, area, trunc(TIME) TIME, TYPE FROM test WHERE TYPE = '签到') a, (SELECT user_name, area, trunc(TIME) TIME, TYPE FROM test WHERE TYPE = '签退') b WHERE a.user_name = b.user_name(+) AND a.time = b.time(+) AND a.user_name = '张三';
from test a, test bwhere a.user_name = b.user_name
and a.type = '签到'
and a.user_name = '张三'
and
To_char(a.time, 'yyyy-MM-dd') =
To_char(b.time, 'yyyy-MM-dd');就行了
FROM (SELECT user_name, area, trunc(TIME) TIME, TYPE FROM test WHERE TYPE = '签到') a,
(SELECT user_name, area, trunc(TIME) TIME, TYPE FROM test WHERE TYPE = '签退') b
WHERE a.user_name = b.user_name(+) AND
a.time = b.time(+) AND
a.user_name = '张三';