这样的日期怎么一一对应

Question

表tb中有数据 inout中I和O要一一对应起来,如下          cardnum    dateshow         timeshow         inout
60010026   2011-4-2 07:34:50 I
         60010026   2011-4-2 07:34:55 I  
60010026   2011-4-2 10:40:23 O  
60010026   2011-4-2 14:53:09 I  
60010026   2011-4-2 17:55:30 O
60010037   2011-4-2 07:40:40 I
60010037   2011-4-2 10:35:20 O
         60010037   2011-4-2 10:40:20 O
60010037   2011-4-2 10:46:09 I
60010037   2011-4-2 16:04:05 O
60010037   2011-4-2 16:21:45 I
60010037   2011-4-2 19:19:44   O想得到
60010026   2011-4-2 07:34:50
60010026   2011-4-2 07:34:55    10:40:23
60010026   2011-4-2 14:53:09    17:55:30
60010037   2011-4-2 07:40:40    10:35:20
60010037   2011-4-2             10:40:20
60010037   2011-4-2 10:46:09    16:04:05
60010037   2011-4-2 16:21:45    19:19:44  
得怎么写select语句呢?

Answer 1

select cardnum,dateshow,timeshow as timeshow1,
       timeshow2=(select top 1 timeshow from tb cardnum=a.cardnum and  
                   timeshow>a.timeshow and inout='O')
from tb a where inout='I'

Answer 2

;with t as (
select '60010026' as cardnum,'2011-4-2' as dateshow,'07:34:50' as timeshow,'I' as inout union all
select '60010026','2011-4-2','07:34:55','I' union all 
select '60010026','2011-4-2','10:40:23','O' union all
select '60010026','2011-4-2','14:53:09','I' union all
select '60010026','2011-4-2','17:55:30','O' union all
select '60010037','2011-4-2','07:40:40','I' union all
select '60010037','2011-4-2','10:35:20','O' union all
select '60010037','2011-4-2','10:40:20','O' union all
select '60010037','2011-4-2','10:46:09','I' union all
select '60010037','2011-4-2','16:04:05','O' union all
select '60010037','2011-4-2','16:21:45','I' union all
select '60010037','2011-4-2','19:19:44','O'),
t1 as (
select *,row_number()over (order by cardnum,dateshow,timeshow asc) as rn from t),
t2 as (
select cardnum,dateshow,
case inout 
when 'I' then timeshow else null end as timein,
(select tb.timeshow from t1 tb where tb.rn=a.rn+1 and tb.inout='O')as timeout
from t1 a) 
select * from t2 where timein is not null or timeout is not null

Answer 3

CREATE TABLE #temp
(
cardnum VARCHAR(10),
dateshow VARCHAR(10),
timeshow VARCHAR(10),
inout CHAR(1)
)
INSERT #temp
select '60010026', '2011-4-2', '07:34:50', 'I' union all
select '60010026', '2011-4-2', '07:34:55', 'I' union all
select '60010026', '2011-4-2', '10:40:23', 'O' union all
select '60010026', '2011-4-2', '14:53:09', 'I' union all
select '60010026', '2011-4-2', '17:55:30', 'O' union all
select '60010037', '2011-4-2', '07:40:40', 'I' union all
select '60010037', '2011-4-2', '10:35:20', 'O' union all
select '60010037', '2011-4-2', '10:40:20', 'O' union all
select '60010037', '2011-4-2', '10:46:09', 'I' union all
select '60010037', '2011-4-2', '16:04:05', 'O' union all
select '60010037', '2011-4-2', '16:21:45', 'I' union all
select '60010037', '2011-4-2', '19:19:44', 'O'
GO
--SQL:
;WITH cte AS
(
SELECT rowno = ROW_NUMBER() OVER(ORDER BY cardnum, dateshow, timeshow), * FROM #temp
)
SELECT A.rowno, A.cardnum, A.dateshow, Itimeshow = A.timeshow, Otimeshow = B.timeshow FROM
(SELECT * FROM cte WHERE inout = 'I') A
OUTER APPLY
(SELECT timeshow FROM cte WHERE inout = 'O' AND rowno = A.rowno + 1) B
UNION ALL
SELECT rowno, cardnum, dateshow, NULL, timeshow FROM cte T
WHERE inout = 'O'
AND NOT EXISTS(SELECT 1 FROM cte WHERE inout = 'I' AND rowno = T.rowno - 1)
ORDER BY rowno
/*
1 60010026 2011-4-2 07:34:50 NULL
2 60010026 2011-4-2 07:34:55 10:40:23
4 60010026 2011-4-2 14:53:09 17:55:30
6 60010037 2011-4-2 07:40:40 10:35:20
8 60010037 2011-4-2 NULL 10:40:20
9 60010037 2011-4-2 10:46:09 16:04:05
11 60010037 2011-4-2 16:21:45 19:19:44
*/

Answer 4

ROW_NUMBER() 是不是2005才有的?  
偶用的是2000

Answer 5

CREATE TABLE #temp
(
    cardnum VARCHAR(10),
    dateshow VARCHAR(10),
    timeshow VARCHAR(10),
    inout CHAR(1)
)
INSERT #temp
select '60010026', '2011-4-2', '07:34:50', 'I' union all
select '60010026', '2011-4-2', '07:34:55', 'I' union all
select '60010026', '2011-4-2', '10:40:23', 'O' union all
select '60010026', '2011-4-2', '14:53:09', 'I' union all
select '60010026', '2011-4-2', '17:55:30', 'O' union all
select '60010037', '2011-4-2', '07:40:40', 'I' union all
select '60010037', '2011-4-2', '10:35:20', 'O' union all
select '60010037', '2011-4-2', '10:40:20', 'O' union all
select '60010037', '2011-4-2', '10:46:09', 'I' union all
select '60010037', '2011-4-2', '16:04:05', 'O' union all
select '60010037', '2011-4-2', '16:21:45', 'I' union all
select '60010037', '2011-4-2', '19:19:44', 'O'
GOselect cardnum,dateshow,timeshow as timeshow1,
       timeshow2=(select top 1 timeshow from #temp where cardnum = a.cardnum and  
                   timeshow>a.timeshow and inout='O')
from #temp a where inout='I'drop table #temp/*cardnum    dateshow   timeshow1  timeshow2
---------- ---------- ---------- ----------
60010026   2011-4-2   07:34:50   10:40:23
60010026   2011-4-2   07:34:55   10:40:23
60010026   2011-4-2   14:53:09   17:55:30
60010037   2011-4-2   07:40:40   10:35:20
60010037   2011-4-2   10:46:09   16:04:05
60010037   2011-4-2   16:21:45   19:19:44(6 行受影响)

Answer 6

CREATE TABLE #temp
(
    cardnum VARCHAR(10),
    dateshow VARCHAR(10),
    timeshow VARCHAR(10),
    inout CHAR(1)
)
INSERT #temp
select '60010026', '2011-4-2', '07:34:50', 'I' union all
select '60010026', '2011-4-2', '07:34:55', 'I' union all
select '60010026', '2011-4-2', '10:40:23', 'O' union all
select '60010026', '2011-4-2', '14:53:09', 'I' union all
select '60010026', '2011-4-2', '17:55:30', 'O' union all
select '60010037', '2011-4-2', '07:40:40', 'I' union all
select '60010037', '2011-4-2', '10:35:20', 'O' union all
select '60010037', '2011-4-2', '10:40:20', 'O' union all
select '60010037', '2011-4-2', '10:46:09', 'I' union all
select '60010037', '2011-4-2', '16:04:05', 'O' union all
select '60010037', '2011-4-2', '16:21:45', 'I' union all
select '60010037', '2011-4-2', '19:19:44', 'O'
GOselect *,rn = identity(int,1,1)
  into #tb
from #tempselect isnull(a.cardnum,b.cardnum) cardnum,isnull(a.dateshow,b.dateshow) dateshow,
a.timeshow as timeshow1,b.timeshow as timeshow2
from (select * from #tb where inout = N'I') a 
full join (select * from #tb where inout = N'O') b on a.rn + 1 = b.rn
order by isnull(a.rn,b.rn)drop table #temp,#tb/*cardnum    dateshow   timeshow1  timeshow2
---------- ---------- ---------- ----------
60010026   2011-4-2   07:34:50   NULL
60010026   2011-4-2   07:34:55   10:40:23
60010026   2011-4-2   14:53:09   17:55:30
60010037   2011-4-2   07:40:40   10:35:20
60010037   2011-4-2   NULL       10:40:20
60010037   2011-4-2   10:46:09   16:04:05
60010037   2011-4-2   16:21:45   19:19:44(7 行受影响)

Answer 7

55555555,还想在用上面的语句建立view的,结果view中不能用identity(int,1,1)