高分:含千万条数据的表查询的性能问题
A表：
symbol       tdate                             tclose
000001         20070725                       1.23
000002         20070726                       0.23
000001         20070723                       2.3
000004         20070618                       1.4
B表:
symbol
000001
000002
000004
输入2007-07-26
输出
symbol           tdate               tclose       lclose
000001           20070725           1.23             2.3
000002           20070726           0.23               NULL
000004           20070618           1.4             NULL  
symbol从B表中取现出,tdate为最新的tclose,
lclose为第二新的tclose,
tclose不为0或NULL


A表的记录数为2000万条左右,现在主要是一个效率的问题.不知道怎么才能再快点.

------解决方案--------------------
说说现在的索引情况
------解决方案--------------------
语句：
select x.*,y.tclose as lclose
from a x left join a y
on a.symbol=b.symbol
and b.tdate=(select max(tdate) from a where symbol=a.symbol and tdate <a.tdate)
where a.tdate <=cast( '2007-7-26 ' as datetime)
and a.tclose <> 0 and a.tclose is not NULL
and not exists (
select 1 from a where symbol=a.symbol
and tdate <=cast( '2007-7-26 ' as datetime)
and tclose <> 0 and tclose is not NULL
and tdate> a.tdate
)

最好有索引(symbol,tdate,tclose)

------解决方案--------------------
晕,我写错了

语句：
select x.*,y.tclose as lclose
from a x left join a y
on x.symbol=y.symbol
and y.tdate=(select max(tdate) from a where symbol=x.symbol and tdate <x.tdate)
where x.tdate <=cast( '2007-7-26 ' as datetime)
and x.tclose <> 0 and x.tclose is not NULL
and not exists (
select 1 from a where symbol=x.symbol
and tdate <=cast( '2007-7-26 ' as datetime)
and tclose <> 0 and tclose is not NULL
and tdate> x.tdate
)

最好有索引(symbol,tdate,tclose)


------解决方案--------------------
要有索引(symbol,tdate,tclose)

------解决方案--------------------
看看这贴，问问这位兄弟

http://community.csdn.net/Expert/topic/5673/5673596.xml?temp=.3719904
------解决方案--------------------
问题没看明白。lclose怎么定义的？
------解决方案--------------------
试试这个.
------------------------------------------------
create table #t (
symbol varchar(10),
tdate varchar(10),
tclose numeric(8,2),
lclose numerice(8,2))

insert #t(symbol,tdate,tclose)
select a.symbol,max(tdate),tclose from a,b where a.symbol=b.symbol