1 3 3 2010-04-23 09:32:43.077 4
1 3 3 2010-04-23 09:32:45.700 1
1 3 3 2010-04-23 09:32:50.983 4
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 3
1 3 3 2010-04-23 09:38:04.840 1
1 3 3 2010-04-23 09:38:06.467 4
3 1 1 2010-04-23 09:32:48.013 1
3 1 1 2010-04-23 09:32:49.513 4
3 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
3 1 1 2010-04-23 09:34:02.997 5
3 1 1 2010-04-23 09:34:10.840 6
3 1 1 2010-04-23 09:37:13.950 1
3 1 1 2010-04-23 09:38:05.670 4现在我抽出如此的N多记录组
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 33 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
并合并成
时间1 时间2 时间3
1 3 3 2010-04-23 09:32:52.873 2010-04-23 09:32:54.247 2010-04-23 09:32:55.107
3 1 1 2010-04-23 09:32:57.060 2010-04-23 09:32:58.293 2010-04-23 09:32:59.027
1 3 3 2010-04-23 09:32:45.700 1
1 3 3 2010-04-23 09:32:50.983 4
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 3
1 3 3 2010-04-23 09:38:04.840 1
1 3 3 2010-04-23 09:38:06.467 4
3 1 1 2010-04-23 09:32:48.013 1
3 1 1 2010-04-23 09:32:49.513 4
3 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
3 1 1 2010-04-23 09:34:02.997 5
3 1 1 2010-04-23 09:34:10.840 6
3 1 1 2010-04-23 09:37:13.950 1
3 1 1 2010-04-23 09:38:05.670 4现在我抽出如此的N多记录组
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 33 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
并合并成
时间1 时间2 时间3
1 3 3 2010-04-23 09:32:52.873 2010-04-23 09:32:54.247 2010-04-23 09:32:55.107
3 1 1 2010-04-23 09:32:57.060 2010-04-23 09:32:58.293 2010-04-23 09:32:59.027
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = (select count(1) from tb where c1 = t.c1 and c2 = t.c2 and c3 = t.c3 and dt < t.dt1) + 1 from tb
) m
group by c1,c2,c3select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = (select count(1) from tb where c1 = t.c1 and c2 = t.c2 and c3 = t.c3 and dt > t.dt1) + 1 from tb
) m
group by c1,c2,c3
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = row_number() over(partition by c1,c2,c3 order by dt) from tb
) m
group by c1,c2,c3select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = row_number() over(partition by c1,c2,c3 order by dt desc) from tb
) m
group by c1,c2,c3
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = (select count(1) from tb where c1 = t.c1 and c2 = t.c2 and c3 = t.c3 and dt < t.dt1) + 1 from tb t
) m
group by c1,c2,c3select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = (select count(1) from tb where c1 = t.c1 and c2 = t.c2 and c3 = t.c3 and dt > t.dt1) + 1 from tb t
) m
group by c1,c2,c3
--sql 2005
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = row_number() over(partition by c1,c2,c3 order by dt) from tb t
) m
group by c1,c2,c3select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = row_number() over(partition by c1,c2,c3 order by dt desc) from tb t
) m
group by c1,c2,c3
以下只是从小到大,从大到小一样写法,只是换成> desc
--sql 2000
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = (select count(1) from
(你的查询
现在我抽出如此的N多记录组
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 33 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
) n where c1 = t.c1 and c2 = t.c2 and c3 = t.c3 and dt < t.dt1) + 1 from
(你的查询
现在我抽出如此的N多记录组
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 33 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
) t
) m
group by c1,c2,c3--sql 2005
select c1,c2,c3,
max(case px when 1 then dt end) dt1,
max(case px when 2 then dt end) dt2,
max(case px when 3 then dt end) dt3
from
(
select t.* , px = row_number() over(partition by c1,c2,c3 order by dt) from
(你的查询
现在我抽出如此的N多记录组
1 3 3 2010-04-23 09:32:52.873 1
1 3 3 2010-04-23 09:32:54.247 2
1 3 3 2010-04-23 09:32:55.107 33 1 1 2010-04-23 09:32:57.060 1
3 1 1 2010-04-23 09:32:58.293 2
3 1 1 2010-04-23 09:32:59.027 3
) t
) m
是呀.. 搞了好久没成功... 所以来请你们帮忙.. 谢谢
规则:
通过时间sort后,在N多记录里,取出最后一个字段值是连续的123,(这个字段是个状态)
INSERT @b(a ,b ,c ,d ,e ) select 1, 3, 3 ,'2010-04-23 09:32:43.077', 4
union all select 1, 3, 3 ,'2010-04-23 09:32:45.700', 1
union all select 1, 3, 3 ,'2010-04-23 09:32:50.983', 4
union all select 1, 3, 3 ,'2010-04-23 09:32:52.873', 1
union all select 1, 3, 3 ,'2010-04-23 09:32:54.247', 2
union all select 1, 3, 3 ,'2010-04-23 09:32:55.107', 3
union all select 1, 3, 3 ,'2010-04-23 09:38:04.840', 1
union all select 1, 3, 3 ,'2010-04-23 09:38:06.467', 4
union all select 3, 1, 1 ,'2010-04-23 09:32:48.013', 1
union all select 3, 1, 1 ,'2010-04-23 09:32:49.513', 4
union all select 3, 1, 1 ,'2010-04-23 09:32:57.060', 1
union all select 3, 1, 1 ,'2010-04-23 09:32:58.293', 2
union all select 3, 1, 1 ,'2010-04-23 09:32:59.027', 3
union all select 3, 1, 1 ,'2010-04-23 09:34:02.997', 5
union all select 3, 1, 1 ,'2010-04-23 09:34:10.840', 6
union all select 3, 1, 1 ,'2010-04-23 09:37:13.950', 1
union all select 3, 1, 1 ,'2010-04-23 09:38:05.670', 4
DECLARE @a table(id INT,a INT,b INT,c INT,d DATETIME)
INSERT @a
SELECT id,a,b,c,d from @b a,
(
SELECT max(id) s,min(id) y FROM @b t GROUP BY (id+e) having(max(id)-min(id))=2
) bb
WHERE a.id BETWEEN y AND sSELECT a,b,c,
(SELECT d FROM @a WHERE a=a.a AND b=a.b AND c=a.c AND id=max(a.id)) 时间1,
(SELECT d FROM @a WHERE a=a.a AND b=a.b AND c=a.c AND id not in(min(a.id),max(a.id))) 时间2,
(SELECT d FROM @a WHERE a=a.a AND b=a.b AND c=a.c AND id=min(a.id)) 时间3
FROM @a a GROUP BY a,b,c
--result
/*a b c 时间1 时间2 时间3
----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------
1 3 3 2010-04-23 09:32:52.873 2010-04-23 09:32:54.247 2010-04-23 09:32:55.107
3 1 1 2010-04-23 09:32:57.060 2010-04-23 09:32:58.293 2010-04-23 09:32:59.027(所影响的行数为 2 行)*/
您好,像那样的记录有很多,去union all每一条,看上去不可能
select id=identity(int,100,-1),* into 临时表 from 你的表
以下部分为@b的用临时表代替即可