求数据配对查询SQL语句？？

Question

数据表的结构如下：id：自动编码
ntype: 数据类型
OccurTime：发生时间
activite： 激活状态（只有 0 和 1 两个值）
要求查询的结果如下：查询规则：
1、ntype的值相同；
2、相邻的两条数据，activite 为 1的 id为beginID,OccurTime  为beginTime,activite 为 0的 id为endID,OccurTime  为endTime；
注意:
有多个开始时间时，以最后一个为准，其余的忽略，有多个结束时间时，以第一个为准，其余的忽略。如何使用SQL语句完成上述功能？？

Answer 1

select beginid,endid,a.ntype,begintime,endtime
(select ntype,max(id)beginid,max(OccurTime)begintime from tb where activite=1 group by ntype)a
left join
(select ntype,min(id)endid,min(OccurTime)endtime from tb where activite=0 group by ntype)b
on a.ntype=b.ntype

Answer 2

缺心眼儿的少了个from-_-select beginid,endid,a.ntype,begintime,endtime from
(select ntype,max(id)beginid,max(OccurTime)begintime from tb where activite=1 group by ntype)a
left join
(select ntype,min(id)endid,min(OccurTime)endtime from tb where activite=0 group by ntype)b
on a.ntype=b.ntype

Answer 3

需求我没有描述清楚，再补充需求描述一下：有多个开始时间时，最后一个开始时间和后面的结束时间配对，前面的开始时间的结束时间为空，有多个结束时间时，以第一个为准，其余的忽略。
数据表的数据如下：要求查询的结果如下：
创建数据表的SQL如下：
CREATE TABLE [dbo].[testTable] (
[id] [int]  NOT NULL ,
[ntype] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OccurTime] [datetime] NULL ,
[activite] [bit] NULL 
) ON [PRIMARY]创建测试数据的SQL语句为：insert testTable (id,ntype,OccurTime,activite)  values ( 1,'lcd','2012-01-01 07:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 2,'lcd','2012-01-01 07:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 3,'hmv','2012-01-01 08:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 4,'hmv','2012-01-01 08:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 5,'lcd','2012-01-01 07:00:35.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 6,'lcd','2012-01-01 07:00:40.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 7,'lcd','2012-01-01 07:00:50.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 8,'lcd','2012-01-01 07:00:55.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 9,'hmv','2012-01-01 09:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 10,'hmv','2012-01-01 09:00:30.000',0)

Answer 4

CREATE TABLE [dbo].[testTable] (
[id] [int]  NOT NULL ,
[ntype] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OccurTime] [datetime] NULL ,
[activite] [bit] NULL 
) ON [PRIMARY]insert testTable (id,ntype,OccurTime,activite)  values ( 1,'lcd','2012-01-01 07:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 2,'lcd','2012-01-01 07:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 3,'hmv','2012-01-01 08:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 4,'hmv','2012-01-01 08:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 5,'lcd','2012-01-01 07:00:35.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 6,'lcd','2012-01-01 07:00:40.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 7,'lcd','2012-01-01 07:00:50.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 8,'lcd','2012-01-01 07:00:55.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 9,'hmv','2012-01-01 09:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 10,'hmv','2012-01-01 09:00:30.000',0)select a.id, b.id,a.ntype,a.OccurTime,b.OccurTime
 from testtable a  
 outer apply(select * from testtable 
         where activite=0 and ntype=a.ntype and a.id=id-1) b
 where a.activite=1
 
/*
id          id          ntype      OccurTime               OccurTime
----------- ----------- ---------- ----------------------- -----------------------
1           2           lcd        2012-01-01 07:00:00.000 2012-01-01 07:00:30.000
3           4           hmv        2012-01-01 08:00:00.000 2012-01-01 08:00:30.000
5           NULL        lcd        2012-01-01 07:00:35.000 NULL
6           7           lcd        2012-01-01 07:00:40.000 2012-01-01 07:00:50.000
9           10          hmv        2012-01-01 09:00:00.000 2012-01-01 09:00:30.000(5 row(s) affected) 

Answer 5

我加了两条数据测试，不过如果你ntype是交叉的话还是不行CREATE TABLE [dbo].[testTable] (
[id] [int]  NOT NULL ,
[ntype] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[OccurTime] [datetime] NULL ,
[activite] [bit] NULL 
) insert testTable (id,ntype,OccurTime,activite)  values ( 1,'lcd','2012-01-01 07:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 2,'lcd','2012-01-01 07:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 3,'hmv','2012-01-01 08:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 4,'hmv','2012-01-01 08:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 5,'lcd','2012-01-01 07:00:35.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 6,'lcd','2012-01-01 07:00:40.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 8,'lcd','2012-01-01 07:00:45.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 9,'lcd','2012-01-01 07:00:50.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 10,'lcd','2012-01-01 07:00:55.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 11,'hmv','2012-01-01 09:00:00.000',1)
insert testTable (id,ntype,OccurTime,activite)  values ( 12,'hmv','2012-01-01 09:00:30.000',0)
insert testTable (id,ntype,OccurTime,activite)  values ( 13,'hmv','2012-01-01 09:00:35.000',0);with cte1 as(
select *,id+ROW_NUMBER() OVER(partition by ntype order by id desc) as rn from [testTable] where activite=0
)
,cte2 as
(select min(id)id,ntype,min(OccurTime)OccurTime from cte1 group by ntype,rn)
select a.id beginid,b.id endid,a.ntype,a.OccurTime begintime,b.OccurTime endtime
from [testTable] a left join  cte2 b on a.ntype=b.ntype and a.id=b.id-1
where a.activite=1 

Answer 6

服务器: 消息 156，级别 15，状态 1，行 3
在关键字 'outer' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 3
第 3 行: 'b' 附近有语法错误。我使用的是SQL 2000 

Answer 7

USE test
GO
-->生成表tbif object_id(N'tb') is not null 
drop table [tb]
Go
Create table [tb]([id] smallint,[ntype] nvarchar(3),[OccurTime] datetime,[activite] nvarchar(1))
Insert into [tb]
Select 1,N'lcd','2012-1-1 7:00:00',N'1'
Union all Select 2,N'lcd','2012-1-1 7:00:30',N'0'
Union all Select 3,N'hmv','2012-1-1 8:00:00',N'1'
Union all Select 4,N'hmv','2012-1-1 8:00:30',N'0'SELECT 
a.ID AS beginID
,b.ID AS endID
,tb.ntype
,a.OccurTime AS begintime
,b.OccurTime AS endtime
FROM 
tb
,(SELECT ntype,ID,OccurTime FROM tb AS a
WHERE activite=1
AND NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.activite=1
AND x.ntype=a.ntype
AND x.OccurTime>a.OccurTime
)
) AS a
,(SELECT ntype,ID,OccurTime FROM tb AS a
WHERE activite=0
AND NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.activite=0
AND x.ntype=a.ntype
AND x.OccurTime<a.OccurTime
)
) AS b
WHERE tb.ntype=a.ntype
AND tb.ntype=b.ntype
GROUP BY tb.ntype,a.ID,a.OccurTime,b.ID,b.OccurTime
ORDER BY begintime
/* 
beginID endID  ntype begintime               endtime
------- ------ ----- ----------------------- -----------------------
1       2      lcd   2012-01-01 07:00:00.000 2012-01-01 07:00:30.000
3       4      hmv   2012-01-01 08:00:00.000 2012-01-01 08:00:30.000
*/

Answer 8

2000
select a.id, b.id,a.ntype,a.OccurTime,b.OccurTime
from (select * from  testTable where activite=1) a left join 
 (select * from  testTable where activite=0) b on a.id=b.id-1 and a.ntype=b.ntype
/*
id          id          ntype      OccurTime               OccurTime
----------- ----------- ---------- ----------------------- -----------------------
1           2           lcd        2012-01-01 07:00:00.000 2012-01-01 07:00:30.000
3           4           hmv        2012-01-01 08:00:00.000 2012-01-01 08:00:30.000
5           NULL        lcd        2012-01-01 07:00:35.000 NULL
6           7           lcd        2012-01-01 07:00:40.000 2012-01-01 07:00:50.000
9           10          hmv        2012-01-01 09:00:00.000 2012-01-01 09:00:30.000(5 row(s) affected) 

Answer 9

USE test
GO
-->生成表tbif object_id(N'tb') is not null 
drop table [tb]
Go
Create table [tb]([id] smallint,[ntype] nvarchar(3),[OccurTime] datetime,[activite] nvarchar(1))
Insert into [tb]
Select 1,N'lcd','2012-1-1 7:00:00',N'1'
Union all Select 2,N'lcd','2012-1-1 7:00:30',N'0'
Union all Select 3,N'hmv','2012-1-1 8:00:00',N'1'
Union all Select 4,N'hmv','2012-1-1 8:00:10',N'1'
Union all Select 5,N'hmv','2012-1-1 8:00:30',N'0'
Union all Select 6,N'mms','2012-1-1 8:00:50',N'1' -- 沒有 endtimeSELECT 
a.ID AS beginID
,b.ID AS endID
,tb.ntype
,a.OccurTime AS begintime
,b.OccurTime AS endtime
FROM 
tb
,(SELECT ntype,ID,OccurTime FROM tb AS a
WHERE activite=1
AND NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.activite=1
AND x.ntype=a.ntype
AND x.OccurTime>a.OccurTime
)
) AS a
LEFT JOIN(SELECT ntype,ID,OccurTime FROM tb AS a
WHERE activite=0
AND NOT EXISTS(SELECT 1 FROM tb AS x
WHERE x.activite=0
AND x.ntype=a.ntype
AND x.OccurTime<a.OccurTime
)
) AS b ON a.ntype=b.ntype
WHERE tb.ntype=a.ntype
GROUP BY tb.ntype,a.ID,a.OccurTime,b.ID,b.OccurTime
ORDER BY begintime
/* 
beginID endID  ntype begintime               endtime
------- ------ ----- ----------------------- -----------------------
1       2      lcd   2012-01-01 07:00:00.000 2012-01-01 07:00:30.000
4       5      hmv   2012-01-01 08:00:10.000 2012-01-01 08:00:30.000
6       NULL   mms   2012-01-01 08:00:50.000 NULL
*/