linux c sql max min,sql-优化使用子查询以获得MIN和MAX

寻找一种更好的方法来编写此查询,因为目前我感觉性能并不理想.我在数据库方面只能做些限制,例如不能在正在使用的表上创建更好的索引-我只是数据的使用者.数据所在的服务器是MS SQL Server 2008R2.

这就是背景,问题又如何呢？

这是我当前的查询：

SELECT

a.ID

,a.[GROUP]

,b.VALUE1

,b.VALUE2

,c.VALUE1

,c.VALUE2

FROM

PRIMARY_TABLE a

LEFT JOIN VALUE_TABLE b ON

a.ID = b.ID

AND a.[GROUP] = b.[GROUP]

AND b.DEPTH = (

SELECT TOP 1 DEPTH

FROM VALUE_TABLE

WHERE ID = a.ID AND [GROUP] = a.[GROUP]

ORDER BY DEPTH ASC

)

LEFT JOIN VALUE_TABLE c ON

a.ID = c.ID

AND a.[GROUP] = c.[GROUP]

AND c.DEPTH = (

SELECT TOP 1 DEPTH

FROM VALUE_TABLE

WHERE ID = a.ID AND [GROUP] = a.[GROUP]

ORDER BY DEPTH DESC

)

为了稍微解释一下,我显然试图在VALUE_TABLE中获得MIN和MAX DEPTHS的关联值. VALUE_TABLE大小适中,> 1百万条记录,所以我正在使用TOP子查询来防止不必要的表扫描…我想知道我是不是弊大于利！

必须进行两次左连接似乎很混乱,我只是想不出一种更干净或更快速的方式来进行此操作…很想听听您的想法！

提前致谢.

PRIMARY和VALUE_TABLE的一些示例数据：

Declare @PRIMARY_TABLE Table([GROUP] varchar(25), [ID] integer, [TYPE] varchar(25));

Insert @PRIMARY_TABLE([GROUP], [ID], [TYPE]) Values

('TEST',123456,'WIDGET')

Declare @VALUE_TABLE Table([GROUP] varchar(25), [ID] integer, [DEPTH] integer, [VALUE1] float, VALUE2 float);

Insert @VALUE_TABLE([GROUP], [ID], [DEPTH], [VALUE1], [VALUE2]) Values

('TEST',123456,2,-89.7,314),

('TEST',123456,3,-89.6,26),

('TEST',123456,4,-89.7,155),

('TEST',123456,5,-89.6,357),

('TEST',123456,6,-89.4,349),

('TEST',123456,7,-89.5,351),

('TEST',123456,8,-89.1,356),

('TEST',123456,9,-89.3,2),

('TEST',123456,10,-89.3,2),

('TEST',123456,11,-89.3,1),

('TEST',123456,12,-89.2,359),

('TEST',123456,13,-89.1,352),

('TEST',123456,14,-89.2,330),

('TEST',123456,15,-89,339),

('TEST',123456,16,-88.5,332),

('TEST',123456,17,-88.8,329),

('TEST',123456,18,-88.4,320),

('TEST',123456,19,-88.5,318),

('TEST',123456,20,-88.3,317),

('TEST',123456,21,-87.8,310),

('TEST',123456,22,-88,311),

('TEST',123456,23,-87.7,304),

('TEST',123456,24,-87.4,305),

('TEST',123456,25,-87.4,301),

('TEST',123456,26,-87.4,303),

('TEST',123456,27,-87.4,306),

('TEST',123456,28,-87.2,299),

('TEST',123456,29,-87.4,301),

('TEST',123456,30,-87.3,302),

('TEST',123456,31,-87.4,299),

('TEST',123456,32,-87.3,298),

('TEST',123456,33,-87,293),

('TEST',123456,34,-87.3,300),

('TEST',123456,35,-87.3,302),

('TEST',123456,36,-87.3,301),

('TEST',123456,37,-87.2,298),

('TEST',123456,38,-87.3,297),

('TEST',123456,39,-87.5,294),

('TEST',123456,40,-87.3,296),

('TEST',123456,41,-87.2,299),

('TEST',123456,42,-87.1,296),

('TEST',123456,43,-87.1,300),

('TEST',123456,44,-87.2,296),

('TEST',123456,45,-87.2,297),

('TEST',123456,46,-87.1,293),

('TEST',123456,47,-87,293),

('TEST',123456,48,-87,297),

('TEST',123456,49,-87.1,298),

('TEST',123456,50,-87.2,291),

('TEST',123456,51,-87.3,297),

('TEST',123456,52,-86.9,293),

('TEST',123456,53,-87.1,298),

('TEST',123456,54,-87,292),

('TEST',123456,55,-87,297),

('TEST',123456,56,-86.8,297),

('TEST',123456,57,-86.9,296),

('TEST',123456,58,-86.8,295),

('TEST',123456,59,-86.9,295),

('TEST',123456,60,-86.8,296),

('TEST',123456,61,-86.6,299),

('TEST',123456,62,-86.5,291),

('TEST',123456,63,-86.6,294),

('TEST',123456,64,-86.8,293),

('TEST',123456,65,-86.8,296),

('TEST',123456,66,-86.7,291),

('TEST',123456,67,-86.7,299),

('TEST',123456,68,-86.7,293),

('TEST',123456,69,-86.7,295),

('TEST',123456,70,-86.7,294),

('TEST',123456,71,-86.8,296),

('TEST',123456,72,-86.5,297),

('TEST',123456,73,-86.6,297),

('TEST',123456,74,-86.5,294),

('TEST',123456,75,-86.4,298),

('TEST',123456,76,-86.5,299),

('TEST',123456,77,-86.4,297),

('TEST',123456,78,-86.3,300),

('TEST',123456,79,-86.4,300),

('TEST',123456,80,-86.7,295),

('TEST',123456,81,-86.6,299),

('TEST',123456,82,-86.6,300),

('TEST',123456,83,-86.6,299),

('TEST',123456,84,-86.5,300),

('TEST',123456,85,-86.4,299),

('TEST',123456,86,-86.3,296),

('TEST',123456,87,-86.2,300),

('TEST',123456,88,-86.2,300),

('TEST',123456,89,-86.2,297),

('TEST',123456,90,-86.1,301),

('TEST',123456,91,-86.3,301),

('TEST',123456,92,-86.2,300),

('TEST',123456,93,-86,301),

('TEST',123456,94,-86.2,302),

('TEST',123456,95,-86.2,301),

('TEST',123456,96,-86.2,304),

('TEST',123456,97,-86.2,303),

('TEST',123456,98,-86,305),

('TEST',123456,99,-86.1,301),

('TEST',123456,100,-86.1,305),

('TEST',123456,101,-86.2,302),

('TEST',123456,102,-86,304),

('TEST',123456,103,-86,303),

('TEST',123456,104,-86.1,307),

('TEST',123456,105,-86.1,306),

('TEST',123456,106,-86.1,305),

('TEST',123456,107,-86.2,302),

('TEST',123456,108,-86.2,307),

('TEST',123456,109,-86.3,309),

('TEST',123456,110,-86.3,303),

('TEST',123456,111,-86.4,304),

('TEST',123456,112,-86.4,308),

('TEST',123456,113,-86.3,310),

('TEST',123456,114,-86.4,304),

('TEST',123456,115,-86.5,307),

('TEST',123456,116,-86.3,308),

('TEST',123456,117,-86.3,309),

('TEST',123456,118,-86.3,306),

('TEST',123456,119,-86.2,311),

('TEST',123456,120,-86,308),

('TEST',123456,121,-85.9,308),

('TEST',123456,122,-86.1,307),

('TEST',123456,123,-86.1,308),

('TEST',123456,124,-86,310),

('TEST',123456,125,-86.2,305),

('TEST',123456,126,-86.1,312),

('TEST',123456,127,-86.2,308),

('TEST',123456,128,-86.1,309),

('TEST',123456,129,-86.1,309),

('TEST',123456,130,-86.2,310),

('TEST',123456,131,-86.5,306),

('TEST',123456,132,-86.5,311),

('TEST',123456,133,-86.6,306),

('TEST',123456,134,-86.8,314),

('TEST',123456,135,-86.7,314),

('TEST',123456,136,-86.8,312),

('TEST',123456,137,-86.7,315),

('TEST',123456,138,-86.9,311),

('TEST',123456,139,-86.8,316),

('TEST',123456,140,-86.9,312),

('TEST',123456,141,-86.9,312),

('TEST',123456,142,-86.7,314),

('TEST',123456,143,-86.7,317),

('TEST',123456,144,-86.7,316),

('TEST',123456,145,-86.7,316),

('TEST',123456,146,-86.8,318),

('TEST',123456,147,-86.8,316),

('TEST',123456,148,-86.8,317),

('TEST',123456,149,-87,317),

('TEST',123456,150,-86.9,315),

('TEST',123456,151,-86.8,321),

('TEST',123456,152,-86.9,319),

('TEST',123456,153,-86.9,320),

('TEST',123456,154,-86.7,318),

('TEST',123456,155,-86.6,316),

('TEST',123456,156,-86.7,317),

('TEST',123456,157,-86.8,319),

('TEST',123456,158,-86.8,317),

('TEST',123456,159,-86.7,318),

('TEST',123456,160,-86.8,316),

('TEST',123456,161,-86.8,317),

('TEST',123456,162,-86.7,320),

('TEST',123456,163,-86.5,318),

('TEST',123456,164,-86.6,315),

('TEST',123456,165,-86.9,316),

('TEST',123456,166,-86.8,317),

('TEST',123456,167,-86.9,316),

('TEST',123456,168,-86.9,317),

('TEST',123456,169,-87,321),

('TEST',123456,170,-87,316),

('TEST',123456,171,-87.1,318),

('TEST',123456,172,-86.9,316),

('TEST',123456,173,-86.9,310),

('TEST',123456,174,-87,315),

('TEST',123456,175,-87,312),

('TEST',123456,176,-87.2,309),

('TEST',123456,177,-87.5,315),

('TEST',123456,178,-87.2,313),

('TEST',123456,179,-87.5,312),

('TEST',123456,180,-87.3,308),

('TEST',123456,181,-87.3,308),

('TEST',123456,182,-87.3,313)

和预期的结果：

123456, TEST, 'WIDGET', -89.7, 314, -87.3, 313