将额外的条件添加到组-by查询会使其变得缓慢。

Question

我有一个查询，它为某个组选择一个最大值。

select * 
from mytable 
where value in (select max(value) from mytable group by mygroupfield)

这是足够快(~ 0.1秒)，但当我添加一个额外的条件，例如。

select * 
from mytable 
where value in (select max(value) from mytable group by mygroupfield)
AND mygroupfield='something'

查询变得非常慢(~ 4-5秒)

value和mygroupfield都是索引的(非唯一的，非集群的，如果重要的话)。这个表包含40,000条记录。

我知道我可以这样做：where value in (select max(value) where mygroupfield='something') (这也是快速的)，但是由于我们的架构，这现在不是一个选择。

有办法加快查询速度吗？

Answer 1

尝试一个相关的子查询，如下例所示。

SELECT a.* 
FROM mytable AS a
WHERE value IN (
    SELECT MAX(value) 
    FROM mytable AS b
    WHERE b.mygroupfield=a.mygroupfield 
    GROUP by mygroupfield
    )
    AND mygroupfield='something';

这一指数也可能有助于：

CREATE INDEX idx ON dbo.mytable(mygroupfield) INCLUDE(value);

Answer 2

您可以尝试使用row_number()，但不确定它是否能达到您的目的

with cte as
(
select *,row_number() over(partition by mygroupfield order by value desc) as rn
from mytable 
where mygroupfield='something'
) 

select * from cte where rn=1

Answer 3

我建议一个相关的子查询：

select t.* 
from mytable t
where t.value = (select max(t2.value)
                 from mytable t2
                 where t2.mygroupfield = t.mygroupfield
                ) and
      t.mygroupfield = 'something';

特别是，这可以利用mytable(mygroupfield, value)上的索引。