一个分组配对的问题

Question

我现在用游标，速度慢，效率很低:if @CarNo=@CarNo_P and @MsgType_P=207 and @MsgType=208
insert into ...
select @CarNo_P=@CarNo,@DateTime_P=@DateTime,@MsgType_P=@MsgType
fetch next from c_Load_DateDiff into @CarNo,@DateTime,@MsgType(_P的变量 表示前一个)请教更好的办法，多谢了！

Answer 1

难道相邻的条件是固定为207与208 ? 
相邻是指 msg_type 的值相邻,还是记录存储顺序相邻?

Answer 2

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b 
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from tb 
where car_no=a.car_no 
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb 
where car_no=b.car_no 
and viewlog_date_time<=b.viewlog_date_time
)

Answer 3

--测试--测试数据
create table tb(car_no int,viewlog_date_time datetime,msg_type int)
insert tb select 200,'2002-06-23 12:08:07.000',207   --a
union all select 200,'2002-06-23 12:17:37.000',208   --b
union all select 200,'2002-06-23 12:34:30.000',207
union all select 200,'2002-06-23 12:45:30.000',208
union all select 200,'2002-06-23 12:50:23.000',207
union all select 200,'2002-06-23 12:50:27.000',207   --c
union all select 200,'2002-06-23 12:58:30.000',208   --d
union all select 200,'2002-06-23 12:59:40.000',208
union all select 200,'2002-06-16 16:25:31.000',207   --e
union all select 205,'2002-06-16 16:33:34.000',208   --f
union all select 205,'2002-06-16 16:44:12.000',207
union all select 205,'2002-06-16 16:53:34.000',208
union all select 205,'2002-06-16 17:14:10.000',207
union all select 205,'2002-06-16 17:20:41.000',208
go--查询
select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b 
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from tb 
where car_no=a.car_no 
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb 
where car_no=b.car_no 
and viewlog_date_time<=b.viewlog_date_time
)
go
--删除测试
drop table tb/*--测试结果car_no      viewlog_date_time          Date_Diff  
----------- -------------------------- -----------
200         2002-06-23 12:08:07.000    9
200         2002-06-23 12:34:30.000    11
200         2002-06-23 12:50:27.000    8
205         2002-06-16 16:44:12.000    9
205         2002-06-16 17:14:10.000    6（所影响的行数为 5 行）
--*/

Answer 4

msg_type 的值都是 207，208， 我现在是按照 car_no,viewlog_date_time 升序排列，得到的第一个结果，然后再把临近的207和208算成是一组。就是麻烦在这里了：（
不知道自己说清楚了没有...

Answer 5

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from tb a join tb b 
on a.car_no=b.car_no
and a.msg_type=207 and b.msg_type=208  --只有两种情形
and (
select count(*) from tb 
where car_no=a.car_no 
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from tb 
where car_no=b.car_no 
and viewlog_date_time<=b.viewlog_date_time
)

Answer 6

我是这样写的：select car_no,viewlog_date_time,msg_type into a# from viewlog_archive
where msg_type in (207,208) 
and year(viewlog_date_time)= '2002'
order by car_no,viewlog_date_time
select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from a# a join a# b 
on a.car_no=b.car_no
and a.msg_type+1=b.msg_type
and (
select count(*) from a# 
where car_no=a.car_no 
and viewlog_date_time<=a.viewlog_date_time
)+1=(
select count(*) from a# 
where car_no=b.car_no 
and viewlog_date_time<=b.viewlog_date_time
)数据库里的记录太多，有280多万，我建了索引，做第一个 a#的查询，用了6秒钟，查出来7万多条数据在a#中。两个加在一块儿，运行了3分钟，还没出来结果，是不是哪里值得优化？

Answer 7

--数据太多了,分开用临时表处理:select car_no,viewlog_date_time
into #t1 from viewlog_archive
where msg_type=207 and year(viewlog_date_time)= '2002'select car_no,viewlog_date_time
into #t2 from viewlog_archive
where msg_type=208 and year(viewlog_date_time)= '2002'select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t1 a join #t2 b 
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t1
where car_no=b.car_no 
and viewlog_date_time<b.viewlog_date_time)

Answer 8

select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t1 a join #t2 b 
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t2  --上面这里改一下
where car_no=b.car_no 
and viewlog_date_time<b.viewlog_date_time)

Answer 9

测试了一下：msg_type in (207,208) 的共有 731922 条数据，查询用时 4 秒配对后的结果 有 363798 ，用时 4分20秒 ：（
而且，有个地方是不是有点儿问题？select max(viewlog_date_time) from #t2  --上面这里改一下
where car_no=b.car_no 
and viewlog_date_time<b.viewlog_date_time)应该是 207 中最大的时间，和 208 中最小的时间，好像 cd 的这种情况。200    2002-06-23 12:50:23.000      207
200    2002-06-23 12:50:27.000      207   --c
200    2002-06-23 12:58:30.000      208   --d
200    2002-06-23 12:59:40.000      208

Answer 10

--没考虑到,用一个临时表select car_no,viewlog_date_time
into #t from viewlog_archive
where msg_type=207 or msg_type=208 and year(viewlog_date_time)= '2002'select a.car_no,a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
from #t a join #t b 
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t
where car_no=b.car_no 
and viewlog_date_time<b.viewlog_date_time)

Answer 11

老大，不行啊，太慢了：（
我把整个存储过程都列出来了：CREATE PROCEDURE [dbo].[sp_rpt_Quick_Meter_Summary_Report] 
@Year int
AS declare @char_year char(4)
set @char_year=convert(char(4),@Year) select car_no,viewlog_date_time
into #t from viewlog_archive
where msg_type=207 or msg_type=208 and year(viewlog_date_time)= @char_year select a.viewlog_date_time
,Date_Diff=datediff(mi,a.viewlog_date_time,b.viewlog_date_time)
into #r from #t a join #t b 
on a.car_no=b.car_no
and a.viewlog_date_time=(
select max(viewlog_date_time) from #t
where car_no=b.car_no 
and viewlog_date_time<b.viewlog_date_time)

select 
sum(case month(Viewlog_Date_Time) when '1' then 1 else 0 end) as Jan,
sum(case month(Viewlog_Date_Time) when '2' then 1 else 0 end) as Feb,
sum(case month(Viewlog_Date_Time) when '3' then 1 else 0 end) as Mar,
sum(case month(Viewlog_Date_Time) when '4' then 1 else 0 end) as Apr,
sum(case month(Viewlog_Date_Time) when '5' then 1 else 0 end) as May,
sum(case month(Viewlog_Date_Time) when '6' then 1 else 0 end) as Jun,
sum(case month(Viewlog_Date_Time) when '7' then 1 else 0 end) as Jul,
sum(case month(Viewlog_Date_Time) when '8' then 1 else 0 end) as Aug,
sum(case month(Viewlog_Date_Time) when '9' then 1 else 0 end) as Sep,
sum(case month(Viewlog_Date_Time) when '10' then 1 else 0 end) as Oct,
sum(case month(Viewlog_Date_Time) when '11' then 1 else 0 end) as Nov,
sum(case month(Viewlog_Date_Time) when '12' then 1 else 0 end) as [Dec] from #r group by Viewlog_Date_Time
GO

Answer 12

第一个临时表中有 731922条数据，是不是慢在这个地方啊  from #t a join #t b运行超过10分钟了，还没出结果:( 欲哭啊~~~