關於這樣的 語句怎麽寫謝謝 尋找SQL 高手
我的 一張表記錄是打卡記錄如
時間 工號(字符型的) 進出
2007-04-27 00:01:00.000 03216 OUT
2007-04-27 00:01:00.00007138 OUT
2007-04-27 00:02:00.00006198 OUT
2007-04-27 00:02:00.00006267 OUT
2007-04-27 00:03:00.00005001 OUT
2007-04-27 00:04:00.00005189 OUT
2007-04-27 00:04:00.00006321 OUT
2007-04-27 00:05:00.00001242 OUT
2007-04-27 00:05:00.00003294 OUT
2007-04-27 00:05:00.00004092 OUT
2007-04-27 00:05:00.00005060 OUT（OUT代表下班）
2007-04-27 16:05:00.00006254 in(in代表上班)
等 等
我現在要抓 所有人上下班從27號到5月5號的 時間
格式這樣的
工號 27日 28日 29日 30日
06462（上班）2007-04-27 00:05:00（下班）2007-04-27 16:05:00 。。。。。。
請問怎麽實現？？？？？？？？？？/急求
[解决办法]
Create table TestID ([time] datetime,ID nvarchar(50),Flag nvarchar(20))
insert into testid
select '2007-04-27 00:01:00.000 ', 03216 , 'OUT '
union select
'2007-04-27 00:01:00.000 ',07138 , 'OUT '
union select
'2007-04-27 00:02:00.000 ',06198 , 'OUT '
union select
'2007-04-27 00:02:00.000 ',06267 , 'OUT '
union select
'2007-04-27 00:03:00.000 ',05001 , 'OUT '
union select
'2007-04-27 00:04:00.000 ',05189 , 'OUT '
union select
'2007-04-27 00:04:00.000 ',06321 , 'OUT '
union select
'2007-04-27 00:05:00.000 ',01242 , 'OUT '
union select
'2007-04-27 00:05:00.000 ',03294 , 'OUT '
union select
'2007-04-27 00:05:00.000 ',04092 , 'OUT '
union select
'2007-04-27 00:05:00.000 ',05060 , 'OUT '
union select
'2007-04-27 16:05:00.000 ',06254 , 'in '
select ID,max((CASE WHEN FLAG= 'in 'THEN [time] END ) )AS EndDate,max((CASE WHEN FLAG= 'OUT 'THEN [time] END )) AS EndDate from TestID
group by ID
[解决办法]
Create table TestID ([time] datetime,ID nvarchar(50),Flag nvarchar(20))
insert into testid
select '2007-04-27 00:01:00.000 ', '03216 ', 'OUT '
union select '2007-04-27 00:01:00.000 ', '07138 ', 'OUT '
union select '2007-04-27 00:02:00.000 ', '06198 ', 'OUT '
union select '2007-04-27 00:02:00.000 ', '06267 ', 'OUT '
union select '2007-04-27 00:03:00.000 ', '05001 ', 'OUT '
union select '2007-04-27 00:04:00.000 ', '05189 ', 'OUT '
union select '2007-04-27 00:04:00.000 ', '06321 ', 'OUT '
union select '2007-04-27 00:05:00.000 ', '01242 ', 'OUT '
union select '2007-04-27 00:05:00.000 ', '03294 ', 'OUT '
union select '2007-04-27 00:05:00.000 ', '04092 ', 'OUT '
union select '2007-04-27 00:05:00.000 ', '05060 ', 'OUT '
union select '2007-04-27 16:05:00.000 ', '06254 ', 'in '
select ID,
max((CASE WHEN FLAG= 'in 'THEN [time] END ) )AS EndDate,
max((CASE WHEN FLAG= 'OUT 'THEN [time] END )) AS EndDate
from TestID
group by ID
