一个关于SQL嵌套查询的问题(100分)

sele aa.*，b.a_idn,b.sum(工时) from a aa left join b group by a_idn on aa.idn=bb.a_idn

循环会快？group by 是会慢要按索引扫描表然后求和；b表的记录数有多少

b表35824条数据,group by 后35677条,.........
sele aa.*，b.a_idn,b.sum(工时) from a aa left join b group by a_idn on aa.idn=bb.a_idn
我试试,再问一下left join 和left outer join是不是一样的

sele aa.*，b.a_idn,b.sum(工时) from a aa left join b group by a_idn on aa.idn=bb.a_idn
按这个格式试过一次了,提示"在关键字 'group' 附近有语法错误。",不行啊

楼主是不是只有b表的数据3w条，那么你可以试试先让a与b的关联的结果插入到临时表，再用临时表进行下面的4个关联，那样应该可以快一些。

如果在select里用子查询呢？比如：
sele aa.*,(select sum(工时) from b where aa.idn=b.a_idn) as 工时
from a aa
（可能更慢！）
凡是有子查询的，一般都慢！

A表37316条记录,取多少数据,视条件而定,或多或少不一定的.
与B表类同的还有C表,有近6万条数据....
本来一个SQL语句就能搞定的,现在弄得烦死人了!

如果是sqlserver2000
那么可以用
declare @temp table(a varchar(10),b int)
insert into @temp select a,b from ab where a='a'
这样的临时表优化嵌套查询

如果在select里用子查询呢？比如：
sele aa.*,(select sum(工时) from b where aa.idn=b.a_idn) as 工时
from a aa
（可能更慢！）
凡是有子查询的，一般都慢！
--------------------------
我试了一下,3万条数据蛮快的,而且一条不少,真是非常感谢你!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

谢谢各位的帮忙!非常感谢!yeskert1的答案解决了我大问题,不然要重写代码了!

那楼主把你原来嵌套的group by 都去掉呢，你那样的写法＆子查询应该没有差别啊，无非是少了个group by。

A表3万条,B表3万条,单纯的left outer join速度很快,就是用group by 速度明显下降,
yeskert1的答案,我试的时候蛮快的,怎么结完贴回去再试,就慢下来了呢,不过比原来的超时,总算是快点了,至少不会超时了.

哈,我发现一个奇怪的现象,用yeskert1的办法,加条件反而比不加条件时慢,
按理说加了条件,才二千条数据,应该更快才对,真是奇怪

用SQL查询分析器,不加条件,14秒就涮出结果了,加了条件,1分半钟已过,结果还没出来.
泪奔~~~~~~~~~~~~~~~~~~~`

你把完整语句贴出来看看

>>加条件反而比不加条件时慢,
这是可能的，因为条件必然有比对的过程，字符串比较是耗时间的。group by a_idn导致慢也是这个原因。
一般，这时应该为表增加索引来改善速度。
能把完整语句贴出来吗？

SELECT *
FROM (SELECT c.*, isnull(d .wcsl, 0) AS wcsl
FROM (SELECT a.*, isnull(a.sl * b.zgs, 0) AS zgs
FROM (SELECT a.*, isnull(b.xzlx, ' ') AS xzlx_sm
FROM (SELECT c.*, isnull(d .qym, ' ') AS qym
FROM (SELECT a.*, isnull(b.xmm, ' ') AS xmm
FROM ww_wwjl a LEFT OUTER JOIN
xmk b ON a.xm_dir = b.dir) c LEFT OUTER JOIN
ww_khdak d ON c.khdm = d .khdm) a LEFT OUTER JOIN
ww_xzlx b ON a.xzlx = b.xzlx_path) a LEFT OUTER JOIN
(SELECT wwjl_idn, SUM(gs) AS zgs
FROM ww_wwjl_gs
WHERE (sbname <> '@#$%')
GROUP BY wwjl_idn) b ON a.idn = b.wwjl_idn) c LEFT OUTER JOIN
(SELECT wwjl_idn, SUM(dhsl) AS wcsl
FROM ww_wwdh
WHERE dhrq >= '2006-01-01' AND dhrq <= '2006-12-31'
GROUP BY wwjl_idn) d ON c.idn = d .wwjl_idn) DERIVEDTBL
WHERE (cj = '机械四课') AND (wwrq >= '2006-01-01') AND (wwrq <= '2006-12-31')
ORDER BY xm_dir, th

------------------
一共六个表:
ww_wwjl是主表,与以下五个表左连接
|---->xmk (ww_wwjl.xm_dir=xmk.dir)
|---->ww_khdak (ww_wwjl.khdm=xmk.khdm)
|---->ww_xzlx (ww_wwjl.xzlx=xmk.xzlx_path)
|---->ww_wwjl_gs (SUM(gs) group by wwjl_idn | ww_wwjl.idn=ww_wwjl_gs.wwjl_idn )
|---->ww_wwdh (SUM(dhsl) group by wwjl_idn | ww_wwjl.idn=ww_wwjl_gs.wwjl_idn )
不知道有没有讲清楚~~~~

头大了！太复杂了！[]

-_-|||,不管了,循环循环

select a.*,b....
from ww_wwjl a left join xmk b on a.xm_dir = b.dir
left join ww_khdak c on a.khdm = c.khdm
left join ww_xzlx d on a.xzlx = d.xzlx_path
left join (SELECT wwjl_idn, SUM(gs) AS zgs
FROM ww_wwjl_gs WHERE (sbname <> '@#$%')
GROUP BY wwjl_idn) e ON a.idn = e.wwjl_idn
left join (SELECT wwjl_idn, SUM(dhsl) AS wcsl
FROM ww_wwdh
WHERE dhrq >= '2006-01-01' AND dhrq <= '2006-12-31'
GROUP BY wwjl_idn) f ON a.idn = f.wwjl_idn
WHERE (cj = '机械四课') AND (wwrq >= '2006-01-01') AND (wwrq <= '2006-12-31')
ORDER BY xm_dir, th