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