oracle 选择不同的返回重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27015408/
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
Select distinct returns duplicates
提问by Cove
I have the following query:
我有以下查询：
SELECT
DISTINCT (TK.TICKETID),
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER,
WF.ASSIGNCODE
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
ORDER BY TK.TICKETID;
But it returns duplicates in TK.TICKETID attribute. And if i remove other attributes all ok. e.g.
但它返回 TK.TICKETID 属性中的重复项。如果我删除其他属性一切正常。例如
TK.TICKETID TK.DESCRIPTION TK.CREATIONDATE TK.REPORTEDBY TK.OWNER WF.ASSIGNCODE
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV NOVIKOVVA
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV PRITULADV
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV SVESHNIKOVAV
M.b. my question is simple but I can't solve it by myself. Will be greatfull for any kind of help.
Mb 我的问题很简单，但我自己无法解决。将非常适合任何形式的帮助。
采纳答案by Horaciux
Try this:
尝试这个：
SELECT
TK.TICKETID,
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER,
max(WF.ASSIGNCODE)
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
group by
TK.TICKETID,
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER
ORDER BY TK.TICKETID;
回答by Jeevan Varughese
There are multiple columns in the select query, the query check for uniqueness across all the columns, not just the one in brackets.
select 查询中有多个列，该查询检查所有列的唯一性，而不仅仅是括号中的那一列。
The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate recordsand fetching only unique records.. Please note it not just columns
SQL DISTINCT 关键字与 SELECT 语句结合使用以消除所有重复记录并仅获取唯一记录。. 请注意它不仅仅是列
If you modify your query to this:
如果您将查询修改为：
SELECT
DISTINCT (TK.TICKETID),
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
ORDER BY TK.TICKETID;
You will get just one result.
你只会得到一个结果。
In the output, please note the following, the starred ones are all different.
在输出中，请注意以下内容，带星号的都是不同的。
TK.TICKETID TK.DESCRIPTION TK.CREATIONDATE TK.REPORTEDBY TK.OWNER **WF.ASSIGNCOD**E
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV **NOVIKOVVA**
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV **PRITULADV**
О1013249 Право доступа 02.06.14 CHERNOVDK SKACHKOVSV **SVESHNIKOVAV**
