Sql 优化问题

[C_SuperMe]

问题的原型是：http://topic.csdn.net/u/20090211/09/692a35c9-5de5-4870-a57d-d5e003c5e39b.html
实际问题的实现代码如下：

select 	t3.code,
	t3.tclose maxTclose,
	t3.tdate maxTdate,
	convert(varchar(10),t0.fistAttentionTime,112) firstAttentionTime
into	#MaxClose
from 	aaa t3
left join bbb t0 on t0.code = t3.code and
	convert(varchar(10),t0.SetUpDate ,120)= convert(varchar(10),getdate(),120) and	  
	t0.IfIssuance = 1 and
	t0.type = 2
where	t3.tdate >= cast(convert(varchar(10),t0.fistAttentionTime,112) as int) and
	not exists(
		select 1 from aaa t4
		where 	t3.code = t4.code and
			t4.tclose >t3.tclose and
			t4.tdate >= convert(varchar(10),t0.fistAttentionTime,112)
			)

select  t1.code,
	t2.maxTclose,
	t2.maxTdate,
	t1.tclose minTclose,
	t1.tdate minTdate,
	(t2.maxTclose-t1.tclose)/t1.tclose rate,
	t2.firstAttentionTime
from	aaa t1
left join  #MaxClose t2 on t2.code = t1.code
where	t1.tdate >= t2.firstAttentionTime and
	t1.tdate <= t2.maxTdate and
	not exists(
		select 1 from aaa t4
		where 	t1.code = t4.code and
			t4.tclose < t1.tclose and
			t4.tdate >= t2.firstAttentionTime and 
			t4.tdate <= t2.maxTdate)
drop table #MaxClose 

说明：aaa 的数据量有450W，bbb的数据量1W左右，现在运行起来非常慢，不知如果优化可以提升一下速度？
备注：aaa--->原型中的a表
bbb--->原型中的b表

[C_SuperMe]

如果大家对http://topic.csdn.net/u/20090211/09/692a35c9-5de5-4870-a57d-d5e003c5e39b.html 中的问题有好多SQL解法，希望可以分享，谢谢

[水族杰纶]

看看

[dawugui]

create table A(id varchar(10), [count] int, time datetime) 
insert into A values('01' ,   10,    '2009-01-01') 
insert into A values('01' ,   6 ,    '2009-02-02') 
insert into A values('01' ,   18,    '2009-02-03') 
insert into A values('01' ,   20,    '2009-01-03') 
insert into A values('01' ,   3 ,    '2009-02-05') 
create table B(id varchar(10) , time datetime)
insert into B values('01' ,  '2009-02-01')
go
select 
  b.id , 
  [max]      = (select max([count]) from a where a.id = b.id and a.time > b.time),
  [max_time] = (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time)),
  [min]      = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time))),
  [min_time] = (select time from a where [count] = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time))))
from b

drop table A , B 

/*
id         max         max_time                                               min         min_time                                               
---------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ 
01         18          2009-02-03 00:00:00.000                                6           2009-02-02 00:00:00.000

（所影响的行数为 1 行）
*/

[爱上松鼠的兔子]

每条都要查一次aaa表，
not exists(
select 1 from aaa t4
where t3.code = t4.code and
t4.tclose >t3.tclose and
t4.tdate >= convert(varchar(10),t0.fistAttentionTime,112)
)
直接取最大值 不就好了，其他项放在group by从句了

[百年树人]

好长，帮顶...

[dawugui]

[Quote=引用 1 楼 C_SuperMe 的回复:]
如果大家对http://topic.csdn.net/u/20090211/09/692a35c9-5de5-4870-a57d-d5e003c5e39b.html 中的问题有好多SQL解法，希望可以分享，谢谢
[/Quote]
看看.

另,你的问题只看代码实在太累,帮顶.

[ermuzi]

是有点长
帮顶