优化使用子查询获取MIN和MAX
优化使用子查询获取MIN和MAX
提问于 2014-12-22 23:14:25
希望找到一种更好的方式来编写这个查询，因为目前我觉得性能并不好。我在数据库方面所能做的有限，例如，我不能为我所使用的表创建更好的索引--我只是数据的使用者。数据所在的服务器是MSSQLServer2008R2。
这就是背景，那么问题呢？
这是我当前的查询：
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中的最小深度和最大深度的相关值。VALUE_TABLE是中等大小的，超过100万条记录，所以我使用顶级子查询来防止不必要的表扫描.不过，我想知道我做的是弊大于利！
这似乎是混乱的，必须要做两个左联合，我只是想不出一个更清洁或更快的方式来做到这一点目前.很想听听你的想法！
提前谢谢。
主数据和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回答 2
Stack Overflow用户
回答已采纳
发布于 2014-12-23 00:08:37
试试这样的..。
;WITH C1 AS
(
SELECT VT.*
,ROW_NUMBER() OVER (ORDER BY VT.DEPTH ASC) rn1
,ROW_NUMBER() OVER (ORDER BY VT.DEPTH DESC) rn2
FROM VALUE_TABLE VT INNER JOIN PRIMARY_TABLE a
ON VT.ID = a.ID AND VT.[GROUP] = a.[GROUP]
)
SELECT a.ID
,a.[GROUP]
,b.VALUE1
,b.VALUE2
,c.VALUE1
,c.VALUE2
FROM PRIMARY_TABLE a
LEFT JOIN C1 b ON b.ID = a.ID
AND b.[GROUP] = a.[GROUP]
AND b.rn1 = 1
LEFT JOIN C1 c ON c.ID = a.ID
AND c.[GROUP] = a.[GROUP]
AND c.rn2 = 1结果
╔════════╦═══════╦════════╦════════╦════════╦════════╗
║ ID ║ GROUP ║ VALUE1 ║ VALUE2 ║ VALUE1 ║ VALUE2 ║
╠════════╬═══════╬════════╬════════╬════════╬════════╣
║ 123456 ║ TEST ║ -89.7 ║ 314 ║ -87.3 ║ 313 ║
╚════════╩═══════╩════════╩════════╩════════╩════════╝Stack Overflow用户
发布于 2014-12-23 00:24:39
试试这个：
;WITH min_max AS(
SELECT
[GROUP],
[ID],
MIN_DEPTH = MIN(DEPTH),
MAX_DEPTH = MAX(DEPTH)
FROM @VALUE_TABLE
GROUP BY [GROUP], [ID]
)
SELECT
p.ID,
p.[GROUP],
p.TYPE,
VALUE1 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE1 END),
VALUE2 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE2 END),
VALUE1 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE1 END),
VALUE2 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE2 END)
FROM @PRIMARY_TABLE p
LEFT JOIN min_max m
ON m.ID = p.ID
AND m.[GROUP] = p.[GROUP]
LEFT JOIN @VALUE_TABLE v
ON v.ID = m.ID
AND v.[GROUP] = m.[GROUP]
GROUP BY p.[GROUP], p.ID, p.[TYPE]编辑:移动CTE上的交叉选项卡
;WITH min_max AS(
SELECT
m.[GROUP],
m.ID,
MIN_VALUE1 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE1 END),
MIN_VALUE2 = MAX(CASE WHEN v.DEPTH = m.MIN_DEPTH THEN v.VALUE2 END),
MAX_VALUE1 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE1 END),
MAX_VALUE2 = MAX(CASE WHEN v.DEPTH = m.MAX_DEPTH THEN v.VALUE2 END)
FROM(
SELECT
[GROUP],
[ID],
MIN_DEPTH = MIN(DEPTH),
MAX_DEPTH = MAX(DEPTH)
FROM @VALUE_TABLE
GROUP BY [GROUP], [ID]
)m
INNER JOIN @VALUE_TABLE v
ON v.ID = m.ID
AND v.[GROUP] = m.[GROUP]
GROUP BY m.[GROUP], m.ID
)
SELECT
p.ID,
p.[GROUP],
p.TYPE,
MIN_VALUE1,
MIN_VALUE2,
MAX_VALUE1,
MAX_VALUE2
FROM @PRIMARY_TABLE p
LEFT JOIN min_max m
ON m.ID = p.ID
AND m.[GROUP] = p.[GROUP]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27611856
复制相关文章
相似问题
腾讯云开发者
