突然想到一个SQL问题，高手请发言(10分)

主表:<br>Create Table MTable (<br>&nbsp; ID Integer,<br>&nbsp; Name varchar2(10),<br>Primary Key(ID)<br>);<br><br>从表:<br>Create Table DTable (<br>&nbsp; ID Integer,<br>&nbsp; Index smallint, --[index in 0..3]<br>&nbsp; Value Number(10,3) not null,<br>Primary Key (ID,Index)<br>)<br>两个表关联字段：ID<br>注：一条主表记录最多有4条从表，最少有1条从表记录。<br>现需要从2表中查询数据，查询条件为Name like 'Z%'，数据按下列格式要求显示：<br>ID, Name, (从表中Index=0的Value) as V0, (从表中Index=1的Value) AS V1, (从表中Index=2的Value) AS V2, (从表中Index=3的Value) AS V3.<br>如何以一个SQL来查询这个数据，且要求速度尽量快。<br>请不要以存储过程来完成。

Select<br>&nbsp; m.ID,<br>&nbsp; m.Name,<br>&nbsp; (Select Value From DTable d Where d.ID = m.ID and d.Index = 0 ) as V0,<br>&nbsp; (Select Value From DTable d Where d.ID = m.ID and d.Index = 1 ) as V1,<br>&nbsp; (Select Value From DTable d Where d.ID = m.ID and d.Index = 2 ) as V2,<br>&nbsp; (Select Value From DTable d Where d.ID = m.ID and d.Index = 3 ) as V3<br>From MTable m<br>Where m.Name like 'Z%'<br><br>[]

忘了说，数据库类型不限。<br>creation-zy的这种写法，当从表的记录数有几百万条的时候，由于每条主表记录需要查询从表4次，所以仍然感觉速度不理想，希望有比这个速度更快的写法。

是否考虑直接<br>Select<br>&nbsp; m.ID,<br>&nbsp; m.Name,d.Index From MTable m<br>Where m.Name like 'Z%'<br>然后写入显示时候,处理成需要的格式

dzl19：当我从表数据量很大的时候，上下翻页时，会速度很慢的。<br>我希望能够通过SQL直接查询出所需要的数据。

如果对速度要求非常高，那么建议在后台用冗余表来存放组合后的信息——而非每次查询<br>时去JION——按照楼主的观点，4次JOIN比1次费力，那么不要JOIN才是理想境界。<br>&nbsp; 数据库优化不止优化Select语句这一个模式——索引、冗余、存储、分割等都可以用。

select a.id,a.name,b.vo,b.v1,b.v2,b.v3 from <br>(select id,name from MTable where name like 'Z%') a<br>inner join<br>(select id,max(case when index=0 then value end)v0,max(case when index=1 then value end)v1,max(case when index=2 then value end)v2,max(case when index=3 then value end)v3 from DTable group by id) b on a.id=b.id

select a.id,a.name,Isnull(sum(v0),0),IsNull(sum(v0),0),IsNull(sum(v0),0)<br>from<br>(<br>SELECT a.id,a.name,case when b.index =0 then sum(b.value) end as v0,<br>case when b.aindex =1 then sum(b.value) end as v1,<br>case when b.aindex =2 then sum(b.value) end as v2<br>FROM MTable a INNER JOIN<br>&nbsp;DTable b ON a.ID = b.ID<br>group by a.id,a.name,b.index<br>) a<br>group by a.id,a.name