可能没有描述清楚,第一张帖子回复只能发三遍,再发一帖
http://topic.csdn.net/u/20110908/23/e77680a7-bb78-4ca0-a67d-acebedb1047e.html有2张表
a (ID,BDBM,WJID,field1,field2,field3) ID是自增长主键
b(BM,BDBM,WJID,bField1,bField2,bField3,.....) BM是自增长主键a和b是一对多的关系,现在主要是获取a表内容,同时获取同条件的b表的第一行数据内容,所以用top1,发现耗时相当慢,才16000多调数据居然花了27秒,如果2表联合查询才1秒左右,大家看看如何写才能加速。原来写法
select a.*,
(select top 1 b.bField1 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField1,
(select top 1 b.bField2 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField2,
(select top 1 b.bField3 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField3,
....from a当从b表取值越多越慢,能不能一次取完,怎么取快些
http://topic.csdn.net/u/20110908/23/e77680a7-bb78-4ca0-a67d-acebedb1047e.html有2张表
a (ID,BDBM,WJID,field1,field2,field3) ID是自增长主键
b(BM,BDBM,WJID,bField1,bField2,bField3,.....) BM是自增长主键a和b是一对多的关系,现在主要是获取a表内容,同时获取同条件的b表的第一行数据内容,所以用top1,发现耗时相当慢,才16000多调数据居然花了27秒,如果2表联合查询才1秒左右,大家看看如何写才能加速。原来写法
select a.*,
(select top 1 b.bField1 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField1,
(select top 1 b.bField2 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField2,
(select top 1 b.bField3 FROM b where b.BDBM=a.BDBM,b.WJID=a.WJID) as bField3,
....from a当从b表取值越多越慢,能不能一次取完,怎么取快些
where a.wjid=t.wjid and t.no=1
b.bField1,
b.bField2,
b.bField3,
....from a
outer apply
(select top 1 * FROM b where b.BDBM=a.BDBM and b.WJID=a.WJID) as b
select m.* , n.* from a m , b n where m.id = n.WJID and not exists (select 1 from b where wjid = n.wjid and bm < n.bm)
*
from a ,b
where
a.id = b.WJID
and
b.BM = (select min(BM) from b t where wjid = n.wjid)
--2005里面用ROW_NUMBER 这个在1楼 不写了
select a.*,
b.bField1,
b.bField2,
b.bField3,
....from a
outer apply
(select top 1 * FROM b where b.BDBM=a.BDBM and b.WJID=a.WJID) as b