SQL 如何在ms access中使用distinct
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5788586/
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 use distinct in ms access
提问by bsm
I have two tables. Task and Categories.
我有两张桌子。任务和类别。
TaskID is not a primary key as there are duplicate values.When there are multiple contacts are selected for a specific task,taskid and other details will be duplicated.I wrote the query:
TaskID 不是主键，因为存在重复值。当为特定任务选择多个联系人时，taskid 和其他详细信息将重复。我写了查询：
SELECT Priority, Subject, Status, DueDate, Completed, Category
FROM Task, Categories
WHERE Categories.CategoryID=Task.CategoryID;
Now as multiple contacts are selected for that task,for the taskid=T4, there are two records(highlighted with gray). I have tried using distinct in ms access 2003 but its not working. I want to display distinct records. (Here there's no requirement to show taskid) If I write :
现在为该任务选择了多个联系人，对于 taskid=T4，有两条记录（以灰色突出显示）。我曾尝试在 ms access 2003 中使用 distinct ，但它不起作用。我想显示不同的记录。（这里不需要显示 taskid）如果我写：
select priority, distinct(subject), .......
and remaining same as mentioned in above query then its giving me an error. I have tried distinctrow also.But didnt get success. How to get distinct values in ms access?
并保持与上面查询中提到的相同，然后它给了我一个错误。我也尝试过distinctrow。但没有成功。如何在ms access中获得不同的值？
回答by bsm
Okay.Its working this way.
好的，它是这样工作的。
SELECT DISTINCT Task.Priority, Task.Subject, Task.Status, Task.DueDate,
Task.Completed, Categories.Category
FROM Task, Categories
WHERE (((Categories.CategoryID)=[Task].[CategoryID]));
回答by Tylor Hess
I don't like using SELECT DISTINCT, I have found that it makes my code take longer to compile. The other way I do it is by using GROUP BY.
我不喜欢使用 SELECT DISTINCT，我发现它使我的代码需要更长的时间来编译。我这样做的另一种方法是使用 GROUP BY。
SELECT Priority, Subject, Status, DueDate, Completed, Category
FROM Task, Categories
WHERE Categories.CategoryID=Task.CategoryID
GROUP BY Subject;
I do not have VBA up at the moment but this should work as well.
我目前没有 VBA，但这也应该有效。
回答by Clon
Using SELECT DISTINCT will work for you, but a better solution here would be to change your database design.
使用 SELECT DISTINCT 对您有用，但这里更好的解决方案是更改您的数据库设计。
Duplicate records may lead to inconsistent data. For example, imagine having two different status in different records with the same TaskID. Which one would be right?
重复的记录可能会导致数据不一致。例如，假设在具有相同 TaskID 的不同记录中有两个不同的状态。哪一个是正确的？
A better design would include something like a Task table, a Contact table and an Assignment table, as follows (the fields in brackets are the PK):
更好的设计将包括任务表、联系人表和分配表之类的内容，如下所示（括号中的字段是 PK）：
Tasks: [TaskID], TaskPriority, Subject, Status, DueDate, Completed, StartDate, Owner, CategoryID, ContactID, ...
任务：[TaskID]、TaskPriority、Subject、Status、DueDate、Completed、StartDate、Owner、CategoryID、ContactID、...
Contact: [ID], Name, Surname, Address, PhoneNumber, ...
联系人：[ID]、姓名、姓氏、地址、电话号码、...
Assignment: [TaskID, ContactID]
分配：[TaskID, ContactID]
Then, you can retrieve the Tasks with a simple SELECT from the Tasks tables. And whenever you need to know the contacts assigned to a Tasks, you would do so using the JOIN clause, like this
然后，您可以使用简单的 SELECT 从任务表中检索任务。每当您需要知道分配给任务的联系人时，您都可以使用 JOIN 子句来这样做，就像这样
SELECT T.*, C.*
FROM TaskID as T
INNER JOIN Assignment as A
ON T.TaskID = A.TaskID
INNER JOIN Contac as C
ON A.ContactID = C.ID
Or similar. You can filter, sort or group the results using all of SQL's query power.
或者类似的。您可以使用 SQL 的所有查询功能对结果进行过滤、排序或分组。
