SELECT Row Values WHERE MAX() 是 GROUP BY 查询中的列值 [英] SELECT Row Values WHERE MAX() is Column Value In GROUP BY Query

问题描述

我该如何选择?我可以创建用户定义的聚合函数吗

How can I select like this? Can I create a User defined Aggregate Function

SELECT Max(A),(SELECT TOP 1 FROM TheGroup Where B=Max(A)) FROM MyTable

SELECT Max(A),(SELECT TOP 1 FROM TheGroup Where B=Max(A)) FROM MyTable

其中 MyTable 如下所示

where MyTable as Shown Below

    A  B  C
--------------
    1  2  S
    3  4  S
    4  5  T
    6  7  T

我想要这样的查询

SELECT MAX(A),(B Where A=Max(A)),C FROM MYTable GROUP BY C

我期待的结果如下

 MAX(A)  Condition    C
-----------------------
   3        4         S
   6        7         T

推荐答案

尝试以下查询:

SELECT TABLE1.A , TABLE2.B , TABLE1.C
FROM 
(
    SELECT MAX(A) AS A,C 
    FROM MYTable 
    GROUP BY C
) AS TABLE1 
INNER JOIN
(
SELECT *
FROM MYTable
) AS TABLE2 ON TABLE1.A = TABLE2.A

SQLFIDDLE

您可以通过简单的连接查询来完成.join 查询总是比 In query 运行得更快.联接查询仅在执行查询时运行一次.我们可以使用 IN 查询存档相同的结果.

you can do it by simple join query . join query always run faster then In query . Join query run only one time at the time of execution of the query . we can archive same result by using IN query .

这篇关于SELECT Row Values WHERE MAX() 是 GROUP BY 查询中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！