T1
ID TIME TYPE
1 2013-6-21 11:12:13 1
2 2013-6-21 11:15:13 1
3 2013-6-21 12:11:13 1
4 2013-6-21 12:18:13 1
1 2013-6-21 11:16:13 1
T2
ID TIME TYPE
1 2013-6-21 11:15:13 0
2 2013-6-21 11:18:13 0
3 2013-6-21 12:13:13 0
1 2013-6-21 11:20:13 0将表T1 和T2组合成表T3
ID TIMEIN TIMEOUT
1 2013-6-21 11:12:13 2013-6-21 11:15:13
1 2013-6-21 11:16:13 2013-6-21 11:20:13
2 2013-6-21 11:15:13 2013-6-21 11:18:13
3 2013-6-21 12:11:13 2013-6-21 12:13:13
4 2013-6-21 12:18:13 NULL请教这样组合该如何实现SELECT A.ID,CASE WHEN A.TIME>=B.TIME THEN A.TIME ELSE NULL END,CASE WHEN A.TIME<=B.TIME THEN B.TIME ELSE NULL END FROM
(SELECT ID,TIME,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME) R FROM T1) A LEFT JOIN (SELECT ID,TIME,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME) R FROM T2) B
ON A.ID=B.ID AND A.R = B.R但是这个语句执行的结果匹配的有问题 请教大神!
Oracle
ID TIME TYPE
1 2013-6-21 11:12:13 1
2 2013-6-21 11:15:13 1
3 2013-6-21 12:11:13 1
4 2013-6-21 12:18:13 1
1 2013-6-21 11:16:13 1
T2
ID TIME TYPE
1 2013-6-21 11:15:13 0
2 2013-6-21 11:18:13 0
3 2013-6-21 12:13:13 0
1 2013-6-21 11:20:13 0将表T1 和T2组合成表T3
ID TIMEIN TIMEOUT
1 2013-6-21 11:12:13 2013-6-21 11:15:13
1 2013-6-21 11:16:13 2013-6-21 11:20:13
2 2013-6-21 11:15:13 2013-6-21 11:18:13
3 2013-6-21 12:11:13 2013-6-21 12:13:13
4 2013-6-21 12:18:13 NULL请教这样组合该如何实现SELECT A.ID,CASE WHEN A.TIME>=B.TIME THEN A.TIME ELSE NULL END,CASE WHEN A.TIME<=B.TIME THEN B.TIME ELSE NULL END FROM
(SELECT ID,TIME,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME) R FROM T1) A LEFT JOIN (SELECT ID,TIME,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TIME) R FROM T2) B
ON A.ID=B.ID AND A.R = B.R但是这个语句执行的结果匹配的有问题 请教大神!
Oracle
SELECT A.ID, A.TIME, b.time
FROM (SELECT ID, TIME, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TIME) R
FROM T1) A
LEFT JOIN (SELECT ID,
TIME,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TIME) R
FROM T2) B
ON A.ID = B.ID
AND A.R = B.R;