有如下表
2010-11-08 08:21:17. 2010-11-08 08:21:20 00BC 南耩井口 毕鹏飞
2010-11-08 08:22:42 2010-11-08 08:23:00 00BC 南耩井口 毕鹏飞
2010-11-08 08:23:03 2010-11-08 08:23:12 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05 2010-11-08 08:27:05 00BC -265码头门 毕鹏飞
2010-11-08 08:28:05 2010-11-08 08:28:08 00BC -265码头门 毕鹏飞
2010-11-08 08:28:11 2010-11-08 08:28:11 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56 2010-11-08 08:59:56 00BC 2中马头门 毕鹏飞
2010-11-08 12:10:43 2010-11-08 12:17:47 00BC 4中马头门 毕鹏飞
2010-11-08 12:21:17 2010-11-08 12:24:20 00BC 南耩井口 毕鹏飞
2010-11-08 12:24:29 2010-11-08 12:41:35 00BC 南耩井口 毕鹏飞
查询结果 :
2010-11-08 08:21:17 2010-11-08 08:23:12 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05 2010-11-08 08:28:11 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56 2010-11-08 12:17:47 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17 2010-11-08 12:41:35 00BC 南耩井口 毕鹏飞
有啥办法啊
2010-11-08 08:21:17. 2010-11-08 08:21:20 00BC 南耩井口 毕鹏飞
2010-11-08 08:22:42 2010-11-08 08:23:00 00BC 南耩井口 毕鹏飞
2010-11-08 08:23:03 2010-11-08 08:23:12 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05 2010-11-08 08:27:05 00BC -265码头门 毕鹏飞
2010-11-08 08:28:05 2010-11-08 08:28:08 00BC -265码头门 毕鹏飞
2010-11-08 08:28:11 2010-11-08 08:28:11 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56 2010-11-08 08:59:56 00BC 2中马头门 毕鹏飞
2010-11-08 12:10:43 2010-11-08 12:17:47 00BC 4中马头门 毕鹏飞
2010-11-08 12:21:17 2010-11-08 12:24:20 00BC 南耩井口 毕鹏飞
2010-11-08 12:24:29 2010-11-08 12:41:35 00BC 南耩井口 毕鹏飞
查询结果 :
2010-11-08 08:21:17 2010-11-08 08:23:12 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05 2010-11-08 08:28:11 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56 2010-11-08 12:17:47 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17 2010-11-08 12:41:35 00BC 南耩井口 毕鹏飞
有啥办法啊
你没有表示列名,所以我就索性写按5个列名进行查询了。
select min(日期1),max(日期2),字段1,字段2,字段3 from tb
group by 字段1,字段2,字段3其实就是个分组,把最小日期,最大日期查询出来即可。
insert @tb
select '2010-11-08 08:21:17', '2010-11-08 08:21:20', '00BC', '南耩井口', '毕鹏飞'
union all select '2010-11-08 08:22:42', '2010-11-08 08:23:00', '00BC', '南耩井口', '毕鹏飞'
union all select '2010-11-08 08:23:03', '2010-11-08 08:23:12 ', '00BC ', '南耩井口 ', '毕鹏飞'
union all select '2010-11-08 08:27:05 ', '2010-11-08 08:27:05 ', '00BC ', '-265码头门 ', '毕鹏飞'
union all select '2010-11-08 08:28:05 ', '2010-11-08 08:28:08 ', '00BC ', '-265码头门 ', '毕鹏飞'
union all select '2010-11-08 08:28:11 ', '2010-11-08 08:28:11 ', '00BC ', '-265码头门 ', '毕鹏飞'
union all select '2010-11-08 08:59:56 ', '2010-11-08 08:59:56 ', '00BC ', '2中马头门 ', '毕鹏飞'
union all select '2010-11-08 12:10:43 ', '2010-11-08 12:17:47 ', '00BC ', '2中马头门 ', '毕鹏飞'
union all select '2010-11-08 12:21:17 ', '2010-11-08 12:24:20 ', '00BC ', '南耩井口 ', '毕鹏飞'
union all select '2010-11-08 12:24:29 ', '2010-11-08 12:41:35 ', '00BC ', '南耩井口 ', '毕鹏飞'select MIN(dt1) dt1,MAX(dt2) dt2,st1,st2,st3
from @tb
group by st1,st2,st3,CONVERT(char(13),dt1,120),CONVERT(char(13),dt2,120)
order by dt1
dt1 dt2 st1 st2 st3
2010-11-08 08:21:17.000 2010-11-08 08:23:12.000 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05.000 2010-11-08 08:28:11.000 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56.000 2010-11-08 08:59:56.000 00BC 2中马头门 毕鹏飞
2010-11-08 12:10:43.000 2010-11-08 12:17:47.000 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17.000 2010-11-08 12:41:35.000 00BC 南耩井口 毕鹏飞
a b c d
1 2 aa bb
3 4 aa bb
5 6 aa bb7 8 cc dd
9 10 cc dd11 12 aa bb
13 14 aa bb以c,d列分组,将连续的记录合并。也就是说 1,2,3,6,7行虽全是 aa, bb的记录, 但中间被 4,5行 cc dd的记录隔断,所以虽然 c,d 列只有两组,但最终结果会有三组。很基础的东西,就不写了。
create table tb(dt1 datetime,dt2 datetime,st1 varchar(100),st2 varchar(100),st3 varchar(100))
insert tb
select '2010-11-08 08:21:17', '2010-11-08 08:21:20', '00BC', '南耩井口', '毕鹏飞' union all
select '2010-11-08 08:22:42', '2010-11-08 08:23:00', '00BC', '南耩井口', '毕鹏飞' union all
select '2010-11-08 08:23:03', '2010-11-08 08:23:12', '00BC', '南耩井口', '毕鹏飞' union all
select '2010-11-08 08:27:05', '2010-11-08 08:27:05', '00BC', '-265码头门', '毕鹏飞' union all
select '2010-11-08 08:28:05', '2010-11-08 08:28:08', '00BC', '-265码头门', '毕鹏飞' union all
select '2010-11-08 08:28:11', '2010-11-08 08:28:11', '00BC', '-265码头门', '毕鹏飞' union all
select '2010-11-08 08:59:56', '2010-11-08 08:59:56', '00BC', '2中马头门', '毕鹏飞' union all
select '2010-11-08 12:10:43', '2010-11-08 12:17:47', '00BC', '2中马头门', '毕鹏飞' union all
select '2010-11-08 12:21:17', '2010-11-08 12:24:20', '00BC', '南耩井口', '毕鹏飞' union all
select '2010-11-08 12:24:29', '2010-11-08 12:41:35', '00BC', '南耩井口', '毕鹏飞'with cte as
(
select row_number() over(order by dt1) as rowid,* from tb
),
cte2 as
(
select row_number() over(order by a.dt2) as rowid,a.dt2,a.st1,a.st2,a.st3 from cte a
left join cte b on a.rowid+1 = b.rowid
where a.st2 <> isnull(b.st2,'') or a.st3 <> isnull(b.st3,'')
),
cte3 as
(
select row_number() over(order by a.dt1) as rowid,a.dt1,a.st1,a.st2,a.st3 from cte a
left join cte b on a.rowid-1 = b.rowid
where a.st2 <> isnull(b.st2,'') or a.st3 <> isnull(b.st3,'')
)
select b.dt1,a.dt2,a.st1,a.st2,a.st3 from cte2 a join cte3 b on a.rowid = b.rowid
/*
dt1 dt2 st1 st2 st3
----------------------- ----------------------- ---- ------- --------
2010-11-08 08:21:17.000 2010-11-08 08:23:12.000 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05.000 2010-11-08 08:28:11.000 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56.000 2010-11-08 12:17:47.000 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17.000 2010-11-08 12:41:35.000 00BC 南耩井口 毕鹏飞(4 行受影响)
*/
(
Select
Row_Number() Over(Order By dt1) AS RowId,
Row_Number() Over(Partition By st1,st2,st3 Order By dt1) AS PartId,dt1,dt2,st1,st2,st3
From tb
) a
Group By a.st1,a.st2,a.st3,a.RowId-a.PartId
Order By 1/*
MinDt MaxDt st1 st2 st3
----------------------- ----------------------- ---- ------- --------
2010-11-08 08:21:17.000 2010-11-08 08:23:12.000 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05.000 2010-11-08 08:28:11.000 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56.000 2010-11-08 12:17:47.000 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17.000 2010-11-08 12:41:35.000 00BC 南耩井口 毕鹏飞(4 行受影响)
*/
(
Select dt1,dt2,st1,st2,st3,
(Select Count(1) From tb t Where t.dt1>= tb.dt1) AS RowId,
(Select Count(1) From tb t Where t.st1=tb.st1 and t.st2=tb.st2 and t.st3=t.st3 and t.dt1>= tb.dt1) AS PartId
From tb
) a
Group By a.st1,a.st2,a.st3,a.RowId-a.PartId
Order By 1/*
MinDt MaxDt st1 st2 st3
----------------------- ----------------------- ---- ------- --------
2010-11-08 08:21:17.000 2010-11-08 08:23:12.000 00BC 南耩井口 毕鹏飞
2010-11-08 08:27:05.000 2010-11-08 08:28:11.000 00BC -265码头门 毕鹏飞
2010-11-08 08:59:56.000 2010-11-08 12:17:47.000 00BC 2中马头门 毕鹏飞
2010-11-08 12:21:17.000 2010-11-08 12:41:35.000 00BC 南耩井口 毕鹏飞(4 行受影响)
*/