SQL - SELECT MAX（）和附带的字段 [英] SQL - SELECT MAX() and accompanying field

问题描述

考虑下面的数据库表

/ p>

  UserID UserName EmailAddress Source 
 3K3S9 Ben [e-mail] user 
 SF13F Harry [e-mail] 3rd_party 
 SF13F Harry [e-mail]用户
 76DSA Lisa [e-mail]用户
 OL39F Nick [e-mail] 3rd_party 
 8F66S Stan [e-mail] user 
 

我需要选择所有字段，但只能选择每个用户一次以及他们的电子邮件地址最大的一个由MAX（）函数确定）。这是我之后的结果...

  UserID UserName EmailAddress Source 
 3K3S9 Ben [e-mail]用户
 SF13F Harry [e-mail] 3rd_party 
 76DSA Lisa [e-mail]用户
 OL39F Nick [e-mail] 3rd_party 
 8F66S Stan [e-mail] user 
  

正如你所看到的，Harry只显示一次与他的最高电子邮件地址相应的

目前发生的情况是，我们对UserID，UserName进行分组，并对EmailAddress和Source使用MAX（），但是这两个字段的最大值dont

我已经通过加入表格本身尝试了另一个过程，但我只能成功地获得正确的电子邮件

任何帮助将被赞赏，因为我花了太长时间试图解决这个已经：）

解决方案

如果您使用SQL Server 2005或更高版本，

  SELECT UserID，UserName，EmailAddress，Source 
 FROM（SELECT UserID，UserName，EmailAddress，Source，
 ROW_NUMBER（）OVER（PARTITION BY UserID 
 ORDER BY EmailAddress DESC） 
 AS RowNumber 
 FROM MyTable）AS a 
 WHERE a.RowNumber = 1 
  

$ b b

当然，没有SQL-Standard排名函数，例如 ROW_NUMBER ，这些SQL Server只能在2005年之后才实现。嵌套依赖查询和自左连接与 ON 包括一个'>'和一个 WHERE ... IS NULL - 但是排序函数使得代码可以被SQL Server引擎可读和（理论上）良好优化。

编辑：这篇文章是一个不错的教程排名，但它使用 RANK ROW_NUMBER （或其他排名函数， DENSE_RANK ）中的当根据排序标准在相同分区中的分组行之间存在联系时，区别是重要的。 此帖能很好地解释差异。

What I have is basically a problem which is easily solved with multiple tables, but I have only a single table to do it.

Consider the following database table

UserID UserName EmailAddress         Source
3K3S9  Ben      [e-mail]        user
SF13F  Harry    [e-mail] 3rd_party
SF13F  Harry    [e-mail]    user
76DSA  Lisa     [e-mail]     user
OL39F  Nick     [e-mail]   3rd_party
8F66S  Stan     [e-mail]        user

I need to select all fields, but only who each user once along with one of their email addresses (the "biggest" one as determined by the MAX() function). This is the result I am after ...

UserID UserName EmailAddress         Source
3K3S9  Ben      [e-mail]        user
SF13F  Harry    [e-mail] 3rd_party
76DSA  Lisa     [e-mail]     user
OL39F  Nick     [e-mail]   3rd_party
8F66S  Stan     [e-mail]        user

As you can see, "Harry" is only shown once with his "highest" email address the correcponding "source"

Currently what is happening is that we are grouping on the UserID, UserName, and using MAX() for the EmailAddress and Source, but the max of those two fields dont always match up, they need to be from the same record.

I have tried another process by joining the table with itself, but I have only managed to get the correct email address but not the corresponding "source" for that address.

Any help would be appreciated as I have spent way too long trying to solve this already :)

解决方案

If you're on SQL Server 2005 or higher,

SELECT  UserID, UserName, EmailAddress, Source
FROM    (SELECT UserID, UserName, EmailAddress, Source,
                ROW_NUMBER() OVER (PARTITION BY UserID
                                   ORDER BY EmailAddress DESC) 
                    AS RowNumber
         FROM   MyTable) AS a
WHERE   a.RowNumber = 1

Of course there are ways to do the same task without the (SQL-Standard) ranking functions such as ROW_NUMBER, which SQL Server implemented only since 2005 -- including nested dependent queries and self left joins with an ON including a '>' and a WHERE ... IS NULL trick -- but the ranking functions make for code that's readable and (in theory) well optimizable by the SQL Server Engine.

Edit: this article is a nice tutorial on ranking, but it uses RANK in the examples instead of ROW_NUMBER (or the other ranking function, DENSE_RANK) -- the distinction matters when there are "ties" among grouped rows in the same partition according to the ordering criteria. this post does a good job explaining the difference.

这篇关于SQL - SELECT MAX（）和附带的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！