如何在 SQL 查询中获取不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14999405/
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
How to get Distinct values in SQL Query
提问by Ahmad Abbasi
I have a problem in my SQL Query
我的 SQL 查询有问题
i want to select DISTINCT StudentGroups(SG) but the query gives me some repetations
我想选择 DISTINCT StudentGroups(SG) 但查询给了我一些重复
here is my Query
这是我的查询
SELECT DISTINCT(SG.SGID), en.EnrollmentID, CR.Name AS Course, INS.Name as Instructor,
S.Session, SG.StartTime, SG.EndTime, EN.CreateDate
FROM StudentGroups SG inner JOIN Enrollments EN ON SG.SGID = EN.SGID
JOIN Courses CR ON SG.CourseID = CR.CourseID
JOIN Class CL ON SG.ClassID = CL.ClassID
JOIN Instructors INS ON SG.InstructorID = INS.InstructorID
JOIN Sessions S ON SG.SessionID = S.SessionID
WHERE EN.SGID NOT IN ( SELECT SGID FROM Enrollments
WHERE StudentID = 45
UPDATED
更新
This query gives me following data
这个查询给了我以下数据
but i don't want repeated SGID
但我不想重复 SGID
回答by
DISTINCTalways applies to all columns returned. Putting parentheses around one column makes no difference in its behavior.
DISTINCT始终适用于返回的所有列。在一列周围放置括号对其行为没有影响。
If you want your results to contain only unique values of SG.GID, you can use a GROUP BYclause instead--but then you have to decide some rules for which values you want to returnin the other columns. You do this by using aggregate functions like MIN(),MAX(),COUNT(),SUM(), etc. Simplified example:
如果您希望结果仅包含 的唯一值SG.GID，则可以改用GROUP BY子句——但随后您必须决定要在其他列中返回哪些值的一些规则。通过使用聚合函数做到这一点喜欢MIN()，MAX()，COUNT()，SUM()，等简单的例子：
SELECT SG.SGID,
MIN(SG.START_TIME), --the lowest start time for this sgid.
COUNT(DISTINCT en.EnrollmentID) --the unique enrollments for this sgid.
FROM StudentGroups SG
INNER JOIN Enrollments EN ON SG.SGID = EN.SGID
GROUP BY SG.SGID;
When joining multiple tables as in your original query, you have to be careful when counting and summing things, as duplicates from the join can give you incorrect results.
当在原始查询中连接多个表时，在计数和求和时必须小心，因为连接中的重复项可能会给您不正确的结果。
Another option would be to use ROW_NUMBER()to return one row for each SGID:
另一种选择是使用ROW_NUMBER()为每个返回一行SGID：
SELECT * FROM (
SELECT SG.SGID,
SG.START_TIME,
en.EnrollmentID,
ROW_NUMBER() OVER (PARTITION BY SGID ORDER BY SG.START_TIME) as RN
FROM StudentGroups SG
INNER JOIN Enrollments EN ON SG.SGID = EN.SGID
)
WHERE RN = 1;
This numbers the rows for each SGID, starting at 1 and sorted by the value of SG.START_TIME. It will return the one row with the earliest start time for each SGID. If multiple rows have the same start time, it will select any one of those, more or less at random. You could add more fields to the ORDER BYclause to further define which rows are returned.
这对每个 的行进行编号SGID，从 1 开始并按 的值排序SG.START_TIME。它将为每个 返回具有最早开始时间的一行SGID。如果多行具有相同的开始时间，它将或多或少随机选择其中的任何一行。您可以向该ORDER BY子句添加更多字段以进一步定义返回哪些行。
回答by Geek
Please use GROUP BY clause when DISTINCT is not working for your desired output.
当 DISTINCT 不能用于您想要的输出时，请使用 GROUP BY 子句。
