在查询中选择大量字段时使用MAX

I understand some varieties of this question have been asked, but I could not find an answer to my specific scenario.

我理解这个问题的一些变种已被问到，但我找不到我的具体情况的答案。

My query has over 50 fields being selected, and only one of them is an aggregate, using MAX(). On the GROUP BY clause, I would only like to pass two specific fields, name and UserID, not all 50 to make the query run. See small subset below.

我的查询有超过50个字段被选中，其中只有一个是聚合，使用MAX（）。在GROUP BY子句中，我只想传递两个特定字段，name和UserID，而不是全部50个以使查询运行。请参阅下面的小部分。

SELECT 
t1.name,
MAX(t2.id) as UserID,
t3.age,
t3.height,
t3.dob,
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
LEFT JOIN table3 t3 ON t1.id = t3.id 
GROUP BY t1.name, UserID

Is there any workaround or better approach to accomplish my goal? The database is SQL Server and any help would be greatly appreciated.

是否有任何解决方法或更好的方法来实现我的目标？数据库是SQL Server，任何帮助将不胜感激。

1 个解决方案

#1

Hmmm . . . What values do you want for the other fields? If you want the max() of one column for each id and code, you can do:

嗯。。。你想要其他领域的价值是多少？如果你想为每个id和代码添加一列的max（），你可以这样做：

select t.*
from (select t.*, max(col) over (partition by id, code) as maxcol
      from t
     ) t
where col = maxcol;

Given that id might be unique, you might want the maximum id as well as the other columns for each code:

鉴于id可能是唯一的，您可能需要每个代码的最大ID以及其他列：

select t.*
from (select t.*, max(id) over (partition by code) as maxid
      from t
     ) t
where id = maxid;

#1