感谢roy_88在http://topic.csdn.net/u/20090204/18/886fde38-bebb-45a3-a6c1-09b8df73cb5a.html中的解答
以刚才的问题继续扩展
表结构如下
id,时间,数据1,数据2,数据3
1 2002-1-12 13:00:01 100 200 300
2 2002-1-12 13:00:02 100 200 300
3 2002-1-12 13:00:03 100 200 300
4 2002-1-12 13:00:04 100 200 300
5 2002-1-12 13:00:05 200 200 300
6 2002-1-12 13:00:06 200 200 300
7 2002-1-12 13:00:07 200 200 300
8 2002-1-12 13:00:08 200 200 300
9 2002-1-12 14:22:01 100 200 300
10 2002-1-12 13:22:02 100 200 300
11 2002-1-12 13:22:03 100 200 300 子表
子表id id 数据
1 1 aaa
2 2 aaa
3 3 aaa
4 4 bbb
5 5 ccc
6 6 aaa
7 7 aaa
8 8 aaa
9 9 bbb
10 10 ccc
11 11 ccc需转换为
id,时间,数据1,数据2,数据3,开关标志(1开始,2结束)
1 2002-1-12 13:00:01 100 200 300 1
2 2002-1-12 13:00:04 100 200 300 2
3 2002-1-12 13:00:05 200 200 300 1
4 2002-1-12 13:00:08 200 200 300 2
5 2002-1-12 14:22:01 100 200 300 1
6 2002-1-12 13:22:03 100 200 300 2 子表id 主表id 数据
1 1 aaa
2 1 aaa
3 1 aaa
4 3 bbb
5 3 ccc
6 3 aaa
7 3 aaa
8 3 aaa
9 4 bbb
10 4 ccc
11 4 ccc
以刚才的问题继续扩展
表结构如下
id,时间,数据1,数据2,数据3
1 2002-1-12 13:00:01 100 200 300
2 2002-1-12 13:00:02 100 200 300
3 2002-1-12 13:00:03 100 200 300
4 2002-1-12 13:00:04 100 200 300
5 2002-1-12 13:00:05 200 200 300
6 2002-1-12 13:00:06 200 200 300
7 2002-1-12 13:00:07 200 200 300
8 2002-1-12 13:00:08 200 200 300
9 2002-1-12 14:22:01 100 200 300
10 2002-1-12 13:22:02 100 200 300
11 2002-1-12 13:22:03 100 200 300 子表
子表id id 数据
1 1 aaa
2 2 aaa
3 3 aaa
4 4 bbb
5 5 ccc
6 6 aaa
7 7 aaa
8 8 aaa
9 9 bbb
10 10 ccc
11 11 ccc需转换为
id,时间,数据1,数据2,数据3,开关标志(1开始,2结束)
1 2002-1-12 13:00:01 100 200 300 1
2 2002-1-12 13:00:04 100 200 300 2
3 2002-1-12 13:00:05 200 200 300 1
4 2002-1-12 13:00:08 200 200 300 2
5 2002-1-12 14:22:01 100 200 300 1
6 2002-1-12 13:22:03 100 200 300 2 子表id 主表id 数据
1 1 aaa
2 1 aaa
3 1 aaa
4 3 bbb
5 3 ccc
6 3 aaa
7 3 aaa
8 3 aaa
9 4 bbb
10 4 ccc
11 4 ccc
同时修改子表的关联id,关联id修改为保留的第一条数据 也就是那条被标记为1的数据
条件如下
1、id连续
2、时间连续
3、数据字段的数据相同
子表id 主表id 数据
1 1 aaa
2 1 aaa
3 1 aaa
4 3 bbb
5 3 ccc
6 3 aaa
7 3 aaa
8 3 aaa
9 4 bbb
10 4 ccc
11 4 ccc
---------------------
4 3 bbb
--------------------
这个为什么不是
4 4 bbb
declare @表 table (id int,时间 datetime ,数据1 int,数据2 int,数据3 int)
insert into @表 select 1,'2002-1-12 13:00:01',100,200,300 union all
select 2,'2002-1-12 13:00:02',100,200,300 union all
select 3,'2002-1-12 13:00:03',100,200,300 union all
select 4,'2002-1-12 13:00:04',100,200,300 union all
select 5,'2002-1-12 13:00:05',200,200,300 union all
select 6,'2002-1-12 13:00:06',200,200,300 union all
select 7,'2002-1-12 13:00:07',200,200,300 union all
select 8,'2002-1-12 13:00:08',200,200,300 union all
select 9,'2002-1-12 14:22:01',100,200,300 union all
select 10,'2002-1-12 14:22:02',100,200,300 union all
select 11,'2002-1-12 14:22:03',100,200,300
select * from (
select * , '1' as 开始标志 from @表 a where not exists
(select 1 from @表 where 数据1=a.数据1 and 数据2=a.数据2 and 数据3=a.数据3
and SUBSTRING( convert(nvarchar(20),时间,120),12,2)=SUBSTRING( convert(nvarchar(20),a.时间,120),12,2) and id<a.id)
union all
select * , 开始标志=2 from @表 a where not exists
(select 1 from @表 where 数据1=a.数据1 and 数据2=a.数据2 and 数据3=a.数据3
and SUBSTRING( convert(nvarchar(20),时间,120),12,2)=SUBSTRING( convert(nvarchar(20),a.时间,120),12,2) and id>a.id)
) a order by id
id 时间 数据1 数据2 数据3 开始标志
----------- ----------------------- ----------- ----------- ----------- -----------
1 2002-01-12 13:00:01.000 100 200 300 1
4 2002-01-12 13:00:04.000 100 200 300 2
5 2002-01-12 13:00:05.000 200 200 300 1
8 2002-01-12 13:00:08.000 200 200 300 2
9 2002-01-12 14:22:01.000 100 200 300 1
11 2002-01-12 14:22:03.000 100 200 300 2(6 行受影响)
select 1是什么意思么
1 1 aaa
2 1 aaa
3 1 aaa
4 3 bbb
5 3 ccc
6 3 aaa
7 3 aaa
8 3 aaa
9 4 bbb
10 4 ccc
11 4 ccc檢查這個結果是否有誤?
go
--> --> (Roy)生成測試數據
set nocount on ;
if not object_id('Tempdb..#') is null
drop table #
Go
Create table #([id] int,[时间] Datetime,[数据1] int,[数据2] int,[数据3] int)
Insert #
select 1,'2002-1-12 13:00:01',100,200,300 union all
select 2,'2002-1-12 13:00:02',100,200,300 union all
select 3,'2002-1-12 13:00:03',100,200,300 union all
select 4,'2002-1-12 13:00:04',100,200,300 union all
select 5,'2002-1-12 13:00:05',200,200,300 union all
--select 6,'2002-1-12 13:00:06',200,200,300 union all --有開沒關時
--select 7,'2002-1-12 13:00:07',200,200,300 union all
--select 8,'2002-1-12 13:00:08',200,200,300 union all
select 9,'2002-1-12 14:22:01',100,200,300 union all
select 10,'2002-1-12 14:22:02',100,200,300 union all
select 11,'2002-1-12 14:22:03',100,200,300--> -->
if not object_id('Tempdb..#2') is null
drop table #2
Go
Create table #2([子表id] int,[id] int,[数据] nvarchar(3))
Insert #2
select 1,1,N'aaa' union all
select 2,2,N'aaa' union all
select 3,3,N'aaa' union all
select 4,4,N'bbb' union all
select 5,5,N'ccc' union all
select 6,6,N'aaa' union all
select 7,7,N'aaa' union all
select 8,8,N'aaa' union all
select 9,9,N'bbb' union all
select 10,10,N'ccc' union all
select 11,11,N'ccc'
Go
select [ID]=row_number()over(order by ID),[时间],[数据1],[数据2],[数据3],开关
from
(select
[id],[时间],[数据1],[数据2],[数据3],1 as 开关
from
# a
where
not exists(select 1 from # where [数据1]=a.[数据1] and [数据2]=a.[数据2] and [数据3]=a.[数据3] and ID=a.ID-1)
union all
select
[id],[时间],[数据1],[数据2],[数据3],2
from
# a
where
not exists(select 1 from # where [数据1]=a.[数据1] and [数据2]=a.[数据2] and [数据3]=a.[数据3] and ID=a.ID+1)
and
exists(select 1 from # where [数据1]=a.[数据1] and [数据2]=a.[数据2] and [数据3]=a.[数据3] and abs(ID-a.ID)=1)
)t
;with t
as
(
select
a.ID,min(b.ID) ID2
from
(select [id] from # a where not exists(select 1 from # where [数据1]=a.[数据1] and [数据2]=a.[数据2] and [数据3]=a.[数据3] and ID=a.ID-1))a
inner join
(select [id] from # a where not exists(select 1 from # where [数据1]=a.[数据1] and [数据2]=a.[数据2] and [数据3]=a.[数据3] and ID=a.ID+1))b on a.ID<=b.ID
group by a.ID
),
t2 as(
select *,row=case when ID=ID2 then 1 else 2 end from t
),
t3
as
(
select b.ID,b.ID2,row=(select sum(row)-case when b.row=1 then 0 else 1 end from t2 where ID<=b.ID)from t2 b )select
a.[子表id],[id]= b.row,a.[数据]
from
#2 a
inner join
t3 b on a.ID between b.ID and b.ID2
select 1 from @表 --表里有多少条数据,就显示多少个1