blocks|key|785139|text|如果使用的是SQL+Server+2005或更高版本，请使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|785140|SELECT+*
++FROM+(
++++++++++++++++SELECT++ID,+
++++++++++++++++++++++++Email,+
++++++++++++++++++++++++ProductName,+
++++++++++++++++++++++++ProductModel,
++++++++++++++++++++++++ROW_NUMBER()+OVER(PARTITION+BY+Email+ORDER+BY+ID+DESC)+rn
++++++++++++++++++++FROM+Products
++++++++++++++)+a
WHERE+rn+=+1|code-block|syntax|javascript|785141|编辑:使用where子句的示例：|785142|SELECT+*
++FROM+(
++++++++++++++++SELECT++ID,+
++++++++++++++++++++++++Email,+
++++++++++++++++++++++++ProductName,+
++++++++++++++++++++++++ProductModel,
++++++++++++++++++++++++ROW_NUMBER()+OVER(PARTITION+BY+Email+ORDER+BY+ID+DESC)+rn
++++++++++++++++++++FROM+Products
+++++++++++++++++++WHERE+ProductModel+=+2
+++++++++++++++++++++AND+ProductName+LIKE+'CYBER%25'

++++++++++++++)+a
WHERE+rn+=+1|785143|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you are using SQL Server 2005 or above use this:

<pre><code>SELECT *
 FROM (
 SELECT ID, 
 Email, 
 ProductName, 
 ProductModel,
 ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
 FROM Products
 ) a
WHERE rn = 1
</code></pre>

EDIT:
Example using a where clause:

<pre><code>SELECT *
 FROM (
 SELECT ID, 
 Email, 
 ProductName, 
 ProductModel,
 ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
 FROM Products
 WHERE ProductModel = 2
 AND ProductName LIKE 'CYBER%'

 ) a
WHERE rn = 1
</code></pre>

blocks|key|999553|text|这假设SQL+Server，并且您对“2005%2B”的定义是给定电子邮件的最大PK|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999554|WITH+CTE+AS
(
SELECT+ID,+
+++++++Email,+
+++++++ProductName,+
+++++++ProductModel,+
+++++++ROW_NUMBER()+OVER+(PARTITION+BY+Email+ORDER+BY+ID+DESC)+AS+RowNumber+
FROM+++Products
)
SELECT+ID,+
+++++++Email,+
+++++++ProductName,+
+++++++ProductModel
FROM+CTE+
WHERE+RowNumber+=+1|code-block|syntax|javascript|999555|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This assumes SQL Server 2005+ and your definition of "last" is the max PK for a given email

<pre><code>WITH CTE AS
(
SELECT ID, 
 Email, 
 ProductName, 
 ProductModel, 
 ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC) AS RowNumber 
FROM Products
)
SELECT ID, 
 Email, 
 ProductName, 
 ProductModel
FROM CTE 
WHERE RowNumber = 1
</code></pre>

blocks|key|785505|text|你可以像这样使用GROUP+BY来解决这个问题：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|785506|SELECT+ID,+Email,+ProductName,+ProductModel
FROM+Products
GROUP+BY+Email|code-block|syntax|javascript|785507|entityMap^0|8|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can over that by using <code>GROUP BY</code> like this:
<pre><code>SELECT ID, Email, ProductName, ProductModel
FROM Products
GROUP BY Email
</code></pre>

blocks|key|999603|text|当您使用DISTINCT时，请将其视为不同的行，而不是列。它将只返回列不完全匹配的行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|999604|SELECT+DISTINCT+ID,+Email,+ProductName,+ProductModel
FROM+Products

----------------------
1+%7C+something@something.com+%7C+ProductName1+%7C+ProductModel1
2+%7C+something@something.com+%7C+ProductName1+%7C+ProductModel1|code-block|syntax|javascript|999605|查询将返回这两行，因为ID列不同。我假设ID列是一个递增的IDENTITY列，如果你想返回最后一个，我建议这样做：|999606|SELECT+DISTINCT+TOP+1+ID,+Email,+ProductName,+ProductModel
FROM+Products
ORDER+BY+ID+DESC|999607|TOP+1将只返回第一条记录，通过按ID降序排列，它将首先返回最后一行的结果。这将为您提供最后一条记录。|999608|entityMap^0|4|8|0|0|B|2|K|2|T|8|0|0|0|5|I|2|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|R|$]]

When you use <code>DISTINCT</code> think of it as a distinct row, not column. It will return only rows where the columns do not match exactly the same.

<pre><code>SELECT DISTINCT ID, Email, ProductName, ProductModel
FROM Products

----------------------
1 | something@something.com | ProductName1 | ProductModel1
2 | something@something.com | ProductName1 | ProductModel1
</code></pre>

The query would return both rows because the <code>ID</code> column is different. I'm assuming that the <code>ID</code> column is an <code>IDENTITY</code> column that is incrementing, if you want to return the last then I recommend something like this:

<pre><code>SELECT DISTINCT TOP 1 ID, Email, ProductName, ProductModel
FROM Products
ORDER BY ID DESC
</code></pre>

The <code>TOP 1</code> will return only the first record, by ordering it by the <code>ID</code> descending it will return the results with the last row first. This will give you the last record.

blocks|key|999639|text|对于Access，您可以使用我在这里提供的SQL+Select查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999640|例如，您有这样一个表：|999641|CLIENTE%7C%7C+NOMBRES+%7C%7C邮件|999642|888+%7C%7C+T800+ARNOLD+%7C%7C+t800.arnold@cyberdyne.com|999643|123+%7C%7C约翰·康纳%7C%7C+s.connor@skynet.com|999644|125+%7C%7C莎拉·康纳%7C%7Cs.connor@skynet.com|999645|并且您只需要选择不同的邮件。你可以这样做：|999646|SQL选择：|999647|SELECT+MAX(p.CLIENTE)+AS+ID_CLIENTE
,+(SELECT+TOP+1+x.NOMBRES+
++++FROM+Rep_Pre_Ene_MUESTRA+AS+x+
++++WHERE+x.MAIL=p.MAIL+
+++++AND+x.CLIENTE=(SELECT+MAX(l.CLIENTE)+FROM+Rep_Pre_Ene_MUESTRA+AS+l+WHERE+x.MAIL=l.MAIL))+AS+NOMBRE,+
p.MAIL
FROM+Rep_Pre_Ene_MUESTRA+AS+p
GROUP+BY+p.MAIL;|code-block|syntax|javascript|999648|您可以使用它来选择最大ID，该最大ID对应的通讯录名称，您可以通过这种方式添加任何其他属性。然后在最后，您将不同的列进行筛选，并且只将其与最后一个不同的列进行分组。|999649|这将为您提供对应数据的最大ID，您可以使用min或任何其他函数，并将该函数复制到子查询。|999650|此select将返回：|999651|CLIENTE%7C%7C+NOMBRES+%7C%7C邮件|999652|888+%7C%7C+T800+ARNOLD+%7C%7C+t800.arnold@cyberdyne.com|999653|125+%7C%7C莎拉·康纳%7C%7Cs.connor@skynet.com|999654|记住索引您选择的列，并且distinct列不能包含全部大写或小写的数字数据，否则它将无法工作。这也只适用于一封挂号邮件。编码快乐！|999655|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1C|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1D|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1E|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1G|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1H|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1I|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1M|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1N|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|1O|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|1P|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

For Access, you can use the SQL Select query I present here:

For example you have this table:

<h2>CLIENTE|| NOMBRES || MAIL</h2>

888 || T800 ARNOLD || t800.arnold@cyberdyne.com

123 || JOHN CONNOR || s.connor@skynet.com

125 || SARAH CONNOR ||s.connor@skynet.com

And you need to select only distinct mails.
You can do it with this:

SQL SELECT:

<pre><code>SELECT MAX(p.CLIENTE) AS ID_CLIENTE
, (SELECT TOP 1 x.NOMBRES 
 FROM Rep_Pre_Ene_MUESTRA AS x 
 WHERE x.MAIL=p.MAIL 
 AND x.CLIENTE=(SELECT MAX(l.CLIENTE) FROM Rep_Pre_Ene_MUESTRA AS l WHERE x.MAIL=l.MAIL)) AS NOMBRE, 
p.MAIL
FROM Rep_Pre_Ene_MUESTRA AS p
GROUP BY p.MAIL;
</code></pre>

You can use this to select the maximum ID, the correspondent name to that maximum ID , you can add any other attribute that way. Then at the end you put the distinct column to filter and you only group it with that last distinct column.

This will bring you the maximum ID with the correspondent data, you can use min or any other functions and you replicate that function to the sub-queries.

This select will return:

<h2>CLIENTE|| NOMBRES || MAIL</h2>

888 || T800 ARNOLD || t800.arnold@cyberdyne.com

125 || SARAH CONNOR ||s.connor@skynet.com

Remember to index the columns you select and the distinct column must have not numeric data all in upper case or in lower case, or else it won't work.
This will work with only one registered mail as well.
Happy coding!!!

blocks|key|785186|text|DISTINCT和GROUP+BY处理整行的原因是您的查询返回整行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|785187|为了帮助你理解:试着手工写出查询应该返回什么，你会发现在非重复的列中放什么是模棱两可的。|785188|如果您真的不关心其他列中的内容，就不要返回它们。对我来说，为每个电子邮件地址返回一个随机行似乎有点无用。|785189|entityMap^0|0|8|9|8|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|R|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|K|$]]

The reason <code>DISTINCT</code> and <code>GROUP BY</code> work on entire rows is that your query returns entire rows. 

To help you understand: Try to write out by hand what the query should return and you will see that it is ambiguous what to put in the non-duplicated columns.

If you literally don't care what is in the other columns, don't return them. Returning a random row for each e-mail address seems a little useless to me.

blocks|key|785532|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|785533|;With+Tab+AS+(SELECT+DISTINCT+Email+FROM++Products)
SELECT+Email,ROW_NUMBER()+OVER(ORDER+BY+Email+ASC)+AS++Id+FROM+Tab
ORDER+BY+Email+ASC|code-block|syntax|javascript|785534|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try This

<pre><code>;With Tab AS (SELECT DISTINCT Email FROM Products)
SELECT Email,ROW_NUMBER() OVER(ORDER BY Email ASC) AS Id FROM Tab
ORDER BY Email ASC
</code></pre>

blocks|key|785546|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|785547|SELECT+ID,+Email,+ProductName,+ProductModel+FROM+Products+WHERE+ID+IN+(SELECT+MAX(ID)+FROM+Products+GROUP+BY+Email)|code-block|syntax|javascript|785548|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>SELECT ID, Email, ProductName, ProductModel FROM Products WHERE ID IN (SELECT MAX(ID) FROM Products GROUP BY Email)
</code></pre>

Let's say I have the following query.

<pre><code>SELECT ID, Email, ProductName, ProductModel FROM Products
</code></pre>

How can I modify it so that it returns no duplicate Emails?

In other words, when several rows contain the same email, I want the results to include only one of those rows (preferably the last one). Duplicates in other columns should be allowed.

Clauses like <code>DISTINCT</code> and <code>GROUP BY</code> appear to work on entire rows. So I'm not sure how to approach this.

DISTINCT for only one column

SQL Server

假设我有以下查询。SELECT ID, Email, ProductName, ProductModel FROM Products我如何修改它，使其不会返回重复的电子邮件？换句话说，当多个行包含相同的电子邮件时，我希望结果只包含其中的一行(最好是最后一行)。应允许其他列中存在重复项。像DISTINCT和GROUP BY这样的子句似乎对整个行都有效。所以我不确定如何处理这个问题。

问只有一列的DISTINCT
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问只有一列的DISTINCTEN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问只有一列的DISTINCT
EN