SQL 同时使用 DISTINCT 和 TOP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15479224/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using DISTINCT and TOP at the same time
提问by user1187282
I want to use distinct & top in the same time , i did
我想同时使用 distinct & top ，我做到了
SELECT distinct TOP 10 * FROM TableA
SELECT distinct TOP 10 * FROM TableA
but i still have a duplicate PersonId so i tought to do :
但我仍然有一个重复的 PersonId 所以我很难做到：
SELECT distinct (personID) TOP 10 * FROM TableAbut the syntax is wrong , so i wonder if there is any solution
SELECT distinct (personID) TOP 10 * FROM TableA但是语法错误，所以我想知道是否有任何解决方案
thanks,
谢谢，
回答by Matt Busche
You're using a SELECT *which is pulling in all records. If you want to use a true DISTINCT only list out the column you want to receive distinct values of. If you have multiple columns then all those columns combined make up one distinct record.
您正在使用一个SELECT *正在提取所有记录。如果你想使用真正的 DISTINCT 只列出你想要接收不同值的列。如果您有多个列，那么所有这些列组合起来构成一个不同的记录。
SELECT distinct TOP 10 personID
FROM TableA
Note that without an ORDER BYthis will return the first 10 records in no particular order. The results could be different each time you run the query.
请注意，如果没有ORDER BYthis，它将以没有特定顺序的方式返回前 10 条记录。每次运行查询时结果可能不同。
回答by Gordon Linoff
You seem to want 10 random records for different persons. Try this:
您似乎想要为不同的人提供 10 条随机记录。尝试这个：
select t.*
from (select t.*,
row_number() over (partition by personid order by (select NULL)) as seqnum
from t
) t
where seqnum = 1
In general, though, when using topyou should also be using an order byto specify what you mean by "top".
但是，一般而言，在使用时，top您还应该使用 anorder by来指定“顶部”的含义。
回答by Pinky Joe
select distinct personid from tablea sample 10
从 tablea 样本 10 中选择不同的 personid
This works in teradata
这适用于teradata
回答by Arulmouzhi
It works simply if you use query like this:
如果您使用这样的查询，它的工作原理很简单：
SELECT DISTINCT TOP 2 name FROM [ATTENDANCE] ;
In the above query, nameis the column_name and [ATTENDANCE]is the table_name.
在上面的查询中，name是 column_name 和[ATTENDANCE]table_name。
You can also use WHEREwith this to make filtering conditions.
您也可以使用WHERE它来制作过滤条件。
回答by Snehal Mankar
SELECT DISTINCT ta.personid FROM (SELECT TOP 10 * FROM TableA) ta
SELECT DISTINCT ta.personid FROM (SELECT TOP 10 * FROM TableA) ta
ta is object of subquery and using ta object we can distinct the values
ta 是子查询的对象，使用 ta 对象我们可以区分值
回答by user1187282
i fixed it i did
我修好了
select distinct personid from (SELECT TOP 10 * FROM TableA)
回答by Vette
If the goal is to select the top 1 record of every personID, then use
如果目标是选择每个 personID 的前 1 条记录，则使用
select * from TableA group by personid
Since you are doing a "group by", it will return every column, but will ignore (not display) any additional rows with the same personId
由于您正在执行“分组依据”，它将返回每一列，但会忽略（不显示）具有相同 personId 的任何其他行
