Sql Server一定时间段只取一条记录

人员表如下
用户    状态     时间
A       进入     2009-7-4 8:45:00
A       进入     2009-7-4 8:46:00
A       离开     2009-7-4 9:00:00
A       离开     2009-7-4 9:01:00
A       进入     2009-7-4 10:01:00
A       进入     2009-7-4 10:03:00
A       离开     2009-7-4 10:30:00
A       离开     2009-7-4 10:31:00
如何取出时间片段内的一条记录,象A在时间2009-7-4 8:45:00和2009-7-4 8:46:00都是进入状态,2009-7-4 9:00:00和2009-7-4 9:01:00都是离开状态,整理数据应该是A在2009-7-4 8:45:00时进入,在2009-7-4 9:01:00时离开,
所以上表如果整理结果为:
A      进入      2009-7-4 8:45:00
A      离开      2009-7-4 9:01:00
A      进入      2009-7-4 10:01:00
A      离开      2009-7-4 10:31:00
如何实现?

21 个解决方案

#1

状态是成对出现？ sql 2000 or 2005?

#2

sql 2000
是这样的,一个读卡设务,记录人员进出的,有可能人员在一定时间内,他不动了,逗留在那里,读卡机不停的上传数据到数据库

#3

select IDENTITy(int,1,1) as id,*
into #a
from tb
go
select 用户,状态,时间
from #a 
where ID%2<>0 and 状态='进入'
union all
select 用户,状态,时间
from #a 
where ID%2=0 and 状态='离开'

#4

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb( 用户 char(1),状态 varchar(10),时间 datetime)
go
insert into tb
select 'a','进入','2009-7-4 8:45:00 ' union all
select 'a','进入','2009-7-4 8:46:00 ' union all
select 'a','离开','2009-7-4 9:00:00 ' union all
select 'a','离开','2009-7-4 9:01:00 ' union all
select 'a','进入','2009-7-4 10:01:00 ' union all
select 'a','进入','2009-7-4 10:03:00 ' union all
select 'a','离开','2009-7-4 10:30:00' union all
select 'a','离开','2009-7-4 10:31:00 ' 

go
select IDENTITy(int,1,1) as id,*
into #a
from tb
go
select 用户,状态,时间
from #a 
where ID%2<>0 and 状态='进入'
union all
select 用户,状态,时间
from #a 
where ID%2=0 and 状态='离开'
/*------------
a	进入	2009-07-04 08:45:00.000
a	进入	2009-07-04 10:01:00.000
a	离开	2009-07-04 09:01:00.000
a	离开	2009-07-04 10:31:00.000
-------*/

#5

引用 3 楼 feixianxxx 的回复:

SQL codeselectIDENTITy(int,1,1)as id,*into #afrom tbgoselect 用户,状态,时间from #awhere ID%2<>0and 状态='进入'unionallselect 用户,状态,时间from #awhere ID%2=0and 状态='离开'

用户    状态    时间 
A      进入    2009-7-4 8:45:00 
A      进入    2009-7-4 8:46:00 
A      进入    2009-7-4 8:46:01
A      进入    2009-7-4 8:46:02 
A      进入    2009-7-4 8:46:03
A      离开    2009-7-4 9:00:00 
A      离开    2009-7-4 9:01:00 
A      进入    2009-7-4 10:01:00 
A      进入    2009-7-4 10:03:00 
A      离开    2009-7-4 10:30:00 
A      离开    2009-7-4 10:31:00 
A      离开    2009-7-4 10:31:01
呢

#6

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb( 用户 char(1),状态 varchar(10),时间 datetime)
go
insert into tb
select 'a','进入','2009-7-4 8:45:00 ' union all
select 'a','进入','2009-7-4 8:46:00 ' union all
select 'a','离开','2009-7-4 9:00:00 ' union all
select 'a','离开','2009-7-4 9:01:00 ' union all
select 'a','进入','2009-7-4 10:01:00 ' union all
select 'a','进入','2009-7-4 10:03:00 ' union all
select 'a','离开','2009-7-4 10:30:00' union all
select 'a','离开','2009-7-4 10:31:00 ' 

go
select IDENTITy(int,1,1) as id,*
into #a
from tb
go
select 用户,状态,时间 from(
select *
from #a 
where ID%2<>0 and 状态='进入'
union all
select *
from #a 
where ID%2=0 and 状态='离开') t
order by id

/*------------
a	进入	2009-07-04 08:45:00.000
a	离开	2009-07-04 09:01:00.000
a	进入	2009-07-04 10:01:00.000
a	离开	2009-07-04 10:31:00.000
------------------*/

#7

引用 5 楼 wangcm888 的回复:

引用 3 楼 feixianxxx 的回复:
SQL codeselectIDENTITy(int,1,1)as id,*into #afrom tbgoselect 用户,状态,时间from #awhere ID%2 <>0and 状态='进入'unionallselect 用户,状态,时间from #awhere ID%2=0and 状态='离开'

用户    状态    时间
A      进入    2009-7-4 8:45:00
A      进入    2009-7-4 8:46:00
A      进入    2009-7-4 8:46:01
A      进入    2009-7-4 8:46:02
A      进入    2009-7-4 8:46:03
A      离开    2009-7-4 9:00:00
A      离开    2009-7-4 9:01:00
A      进入    2009-7-4 10:01:00
A      进入    2009-7-4 10:03:00
A      离开    2009-7-4 10:30:00
A      离开    2009-7-4 10:31:00
A      离开    2009-7-4 10:31:01
呢

不是成双成对出现的？

#8

这用sql得用游标处理吧。没有啥规律，处理起来估计很慢。哈哈

#9

declare @tb table (用户 nvarchar(10),状态 nvarchar(10),时间 datetime)
insert into @tb select 'a','进入','2009-7-4 8:45:00'
    union all   select 'a','进入','2009-7-4 8:46:00'
    union all   select 'a','离开','2009-7-4 9:00:00'
    union all   select 'a','离开','2009-7-4 9:01:00'
    union all   select 'a','进入','2009-7-4 10:01:00'
    union all   select 'a','进入','2009-7-4 10:03:00'
    union all   select 'a','离开','2009-7-4 10:30:00'    
    union all   select 'a','离开','2009-7-4 10:31:00'
select * from (
select * from @tb a where 状态='进入'  and not exists 
         ( select  1 from @tb where a.用户=用户 and a.状态=状态 and SUBSTRING(convert(nvarchar(20),a.时间,121),13,1)=SUBSTRING(convert(nvarchar(20),时间,121),13,1) and 时间<a.时间 )
union all
select * from @tb a where 状态='离开'  and not exists 
         ( select  1 from @tb where a.用户=用户 and a.状态=状态 and SUBSTRING(convert(nvarchar(20),a.时间,121),13,1)=SUBSTRING(convert(nvarchar(20),时间,121),13,1) and 时间>a.时间 )) tb order by 时间




(8 行受影响)
用户         状态         时间
---------- ---------- -----------------------
a          进入         2009-07-04 08:45:00.000
a          离开         2009-07-04 09:01:00.000
a          进入         2009-07-04 10:01:00.000
a          离开         2009-07-04 10:31:00.000

(4 行受影响)

#10

DECLARE @TB TABLE([用户] VARCHAR(1), [状态] NVARCHAR(2), [时间] DATETIME)
INSERT @TB 
SELECT 'A', N'进入', '2009-7-4 8:45:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 8:46:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 9:00:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 9:01:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 10:01:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 10:03:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 10:30:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 10:31:00'

SELECT *,0 AS GRP INTO # FROM @TB ORDER BY [用户],[时间]

DECLARE @STATUS NVARCHAR(2), @USER VARCHAR(1), @GRP INT
SET @GRP=0

UPDATE #
  SET @GRP=CASE WHEN @USER=[用户] AND @STATUS=[状态] THEN @GRP ELSE @GRP+1 END,
	  @USER=[用户],
	  @STATUS=[状态],
	  GRP=@GRP

SELECT [用户],[状态],MIN([时间]) AS [时间]
FROM #
GROUP BY [用户],[状态],GRP
ORDER BY GRP

DROP TABLE #
/*
用户   状态   时间
---- ---- -----------------------
A    进入   2009-07-04 08:45:00.000
A    离开   2009-07-04 09:00:00.000
A    进入   2009-07-04 10:01:00.000
A    离开   2009-07-04 10:30:00.000
*/

#11

引用 5 楼 wangcm888 的回复:

引用 3 楼 feixianxxx 的回复:
 SQL codeselectIDENTITy(int,1,1)as id,*into #afrom tbgoselect 用户,状态,时间from #awhere ID%2 <>0and 状态='进入'unionallselect 用户,状态,时间from #awhere ID%2=0and 状态='离开'

 用户    状态    时间
 A      进入    2009-7-4 8:45:00
 A      进入    2009-7-4 8:46:00
 A      进入    2009-7-4 8:46:01
 A      进入    2009-7-4 8:46:02
 A      进入    2009-7-4 8:46:03
 A      离开    2009-7-4 9:00:00
 A      离开    2009-7-4 9:01:00
 A      进入    2009-7-4 10:01:00
 A      进入    2009-7-4 10:03:00
 A      离开    2009-7-4 10:30:00
 A      离开    2009-7-4 10:31:00
 A      离开    2009-7-4 10:31:01
 呢

参考10楼。

#12

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb( 用户 char(1),状态 varchar(10),时间 datetime)
go
insert into tb
select 'a','进入','2009-7-4 8:45:00 ' union all
select 'a','进入','2009-7-4 8:46:01 ' union all
select 'a','进入','2009-7-4 8:46:02 ' union all
select 'a','进入','2009-7-4 8:46:03 ' union all
select 'a','离开','2009-7-4 9:00:00 ' union all
select 'a','离开','2009-7-4 9:01:00 ' union all
select 'a','离开','2009-7-4 9:01:01 ' union all
select 'a','进入','2009-7-4 10:01:00 ' union all
select 'a','进入','2009-7-4 10:03:00 ' union all
select 'a','进入','2009-7-4 10:03:01 ' union all
select 'a','离开','2009-7-4 10:30:02' union all
select 'a','离开','2009-7-4 10:30:03' union all
select 'a','离开','2009-7-4 10:31:04 ' 

go
SELECT *,0 AS GRP INTO # FROM tb ORDER BY [用户],[时间]
select * from #

DECLARE @STATUS NVARCHAR(2), @USER VARCHAR(1), @GRP INT
SET @GRP=0

UPDATE #
  SET @GRP=CASE WHEN @USER=[用户] AND @STATUS=[状态] THEN @GRP ELSE @GRP+1 END,
      @USER=[用户],
      @STATUS=[状态],
      GRP=@GRP
select [用户],[状态],[时间]
from (
SELECT [用户],[状态],MIN([时间]) AS [时间],GRP
FROM #
where 状态='进入'
GROUP BY [用户],[状态],GRP 
union all
SELECT [用户],[状态],Max([时间]) AS [时间],GRP 
FROM #
where 状态='离开'
GROUP BY [用户],[状态],GRP )t
order by GRP



/*------------
a	进入	2009-07-04 08:45:00.000
a	离开	2009-07-04 09:01:01.000
a	进入	2009-07-04 10:01:00.000
a	离开	2009-07-04 10:31:04.000
------------------*/

#13

引用 11 楼 csdyyr 的回复:

引用 5 楼 wangcm888 的回复:
引用 3 楼 feixianxxx 的回复:
SQL codeselectIDENTITy(int,1,1)as id,*into #afrom tbgoselect 用户,状态,时间from #awhere ID%2 <>0and 状态='进入'unionallselect 用户,状态,时间from #awhere ID%2=0and 状态='离开'

用户    状态    时间
A      进入    2009-7-4 8:45:00
A      进入    2009-7-4 8:46:00
A      进入    2009-7-4 8:46:01
A      进入    2009-7-4 8:46:02
A      进入    2009-7-4 8:46:03
A      离开    2009-7-4 9:00:00
A      离开    2009-7-4 9:01:00
A      进入    2009-7-4 10:01:00
A      进入    2009-7-4 10:03:00
A      离开    2009-7-4 10:30:00
A      离开    2009-7-4 10:31:00
A      离开    2009-7-4 10:31:01
呢


参考10楼。

稍微有点小错误。。离开要取最大的。

#14

楼上很细心。

#15

DECLARE @TB TABLE([用户] VARCHAR(1), [状态] NVARCHAR(2), [时间] DATETIME)
INSERT @TB 
SELECT 'A', N'进入', '2009-7-4 8:45:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 8:46:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 8:46:01' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 8:46:02' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 8:46:03' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 9:00:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 9:01:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 10:01:00' UNION ALL 
SELECT 'A', N'进入', '2009-7-4 10:03:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 10:30:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 10:31:00' UNION ALL 
SELECT 'A', N'离开', '2009-7-4 10:31:01'

SELECT *,0 AS GRP INTO # FROM @TB ORDER BY [用户],[时间]

DECLARE @STATUS NVARCHAR(2), @USER VARCHAR(1), @GRP INT
SET @GRP=0

UPDATE #
  SET @GRP=CASE WHEN @USER=[用户] AND @STATUS=[状态] THEN @GRP ELSE @GRP+1 END,
	  @USER=[用户],
	  @STATUS=[状态],
	  GRP=@GRP

SELECT [用户],[状态],CASE WHEN [状态]=N'进入' THEN MIN([时间]) ELSE MAX([时间]) END AS [时间]
FROM #
GROUP BY [用户],[状态],GRP
ORDER BY [用户],GRP

DROP TABLE #
/*
用户   状态   时间
---- ---- -----------------------
A    进入   2009-07-04 08:45:00.000
A    离开   2009-07-04 09:01:00.000
A    进入   2009-07-04 10:01:00.000
A    离开   2009-07-04 10:31:01.000
*/

#16

进入取最早，离开取最大。

#17

用RowNumber_Over函数，遍历数据表的每一行，如果发现状态不通立即生成生成A      进入      2009-7-4 8:45:00 
或离开      2009-7-4 8:46:00 
这样我觉得比较易懂

#18

借助临时表处理。

#19

我来说下情况 楼主这个查询大体看来应该用游标处理，
其实这个表设计的也不合理，你后面应该加一列  就是他同一次进入的次数
否则你表的排序变化了 ，他的 结构也会变化是没办法用SQL语句来分析的，
逻辑上就是不可以区分的，


如果你的排序是按照现在的固定不变的，我个人感觉应该用游标处理，但是具体处理方法我也不太清楚
只是说个思路。

#20

感觉你的设备有问题吧

只有2个状态（待进，待出）
既然进去了，状态就是已经进入了（也就是说设备应该自动将卡转到待出状态），不可能还是待进状态，只能是待出状态，
怎么可能进入后，卡还是处在待进状态？这样不出乱在就怪了
 就像楼主所言，某卡，在某段时间内一直在打卡，那就是一直在进入，数据库就记录了N次进入

个人认为应该没有这样弱智设备吧

#21

如过不是设备问题
数据库应该记录成

进
出
进
出
并且 每次进出中间间隔时间应该是很短暂的