SQL 如果有多个重复行，如何仅返回 1 行并且仍然返回不重复的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1442321/
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 return only 1 row if multiple duplicate rows and still return rows that are not duplicates?
提问by Himadri
I have a temptable that looks like this:
我有一个看起来像这样的诱惑：
RequestID | CreatedDate | HistoryStatus
CF-0000001 | 8/26/2009 1:07:01 PM | For Review
CF-0000001 | 8/26/2009 1:07:01 PM | Completed
CF-0000112 | 8/26/2009 1:07:01 PM | For Review
CF-0000113 | 8/26/2009 1:07:01 PM | For Review
CF-0000114 | 8/26/2009 1:07:01 PM | Completed
CF-0000115 | 8/26/2009 1:07:01 PM | Completed
And how I'd like the table to look at the end is like this:
我希望表格最后的样子是这样的：
RequestID | CreatedDate | HistoryStatus
CF-0000001 | 8/26/2009 1:07:01 PM | Completed
CF-0000112 | 8/26/2009 1:07:01 PM | For Review
CF-0000113 | 8/26/2009 1:07:01 PM | For Review
CF-0000114 | 8/26/2009 1:07:01 PM | Completed
CF-0000115 | 8/26/2009 1:07:01 PM | Completed
I.e. the duplicate CF-0000001should be removed.
即CF-0000001应该删除重复项。
How can I return or should i say choose only ONE row if there are multiple duplicate rows and still return rows that are not duplicates?
如果有多个重复行并且仍然返回不重复的行，我该如何返回或者我应该说只选择一行？
回答by Himadri
Try this if you want to display one of duplicate rows based on RequestID and CreatedDate and show the latest HistoryStatus.
如果您想根据 RequestID 和 CreatedDate 显示重复行之一并显示最新的 HistoryStatus，请尝试此操作。
with t as (select row_number()over(partition by RequestID,CreatedDate order by RequestID) as rnum,* from tbltmp)
Select RequestID,CreatedDate,HistoryStatus from t a where rnum in (SELECT Max(rnum) FROM t GROUP BY RequestID,CreatedDate having t.RequestID=a.RequestID)
or if you want to select one of duplicate rows considering CreatedDate only and show the latest HistoryStatus then try the query below.
或者，如果您想选择仅考虑 CreatedDate 的重复行之一并显示最新的 HistoryStatus，请尝试以下查询。
with t as (select row_number()over(partition by CreatedDate order by RequestID) as rnum,* from tbltmp)
Select RequestID,CreatedDate,HistoryStatus from t where rnum = (SELECT Max(rnum) FROM t)
Or if you want to select one of duplicate rows considering Request ID only and show the latest HistoryStatus then use the query below
或者，如果您想选择仅考虑请求 ID 的重复行之一并显示最新的 HistoryStatus，请使用下面的查询
with t as (select row_number()over(partition by RequestID order by RequestID) as rnum,* from tbltmp)
Select RequestID,CreatedDate,HistoryStatus from t a where rnum in (SELECT Max(rnum) FROM t GROUP BY RequestID,CreatedDate having t.RequestID=a.RequestID)
All the above queries I have written in sql server 2005.
我在 sql server 2005 中编写的所有上述查询。
回答by llamaoo7
From the title I'm guessing you only need one result per unique row? If this is the case, take a look at the GROUP BYclause (or SELECT DISTINCT).
从标题我猜你只需要每行一个结果？如果是这种情况，请查看GROUP BY子句（或SELECT DISTINCT）。
回答by RedFilter
select t.*
from (
select RequestID, max(CreatedDate) as MaxCreatedDate
from table1
group by RequestID
) tm
inner join table1 t on tm.RequestID = t.RequestID and tm.MaxCreatedDate = t.CreatedDate
回答by Arthur Ronald
If you have a one to many relationship in your query, duplicate rows may occurs on one side.
如果查询中存在一对多关系，则一侧可能会出现重复的行。
Suppose the following
假设以下
TABLE TEAM
ID TEAM_NAME
0 BULLS
1 LAKERS
TABLE PLAYER
ID TEAM_ID PLAYER_NAME
0 0 JORDAN
1 0 PIPPEN
And you execute a query like
然后你执行一个查询
SELECT
TEAM.TEAM_NAME,
PLAYER.PLAYER_NAME
FROM TEAM
INNER JOIN PLAYER
You will get
你会得到
TEAM_NAME PLAYER_NAME
BULLS JORDAN
BULLS PIPPEN
So you will have duplicate TEAM NAME. Even using DISTINCT clause, your result set will contain duplicate TEAM NAME
因此，您将拥有重复的团队名称。即使使用 DISTINCT 子句，您的结果集也会包含重复的 TEAM NAME
So if you do not want duplicate TEAM_NAME in your query, do the following
因此，如果您不想在查询中重复 TEAM_NAME，请执行以下操作
SELECT ID, TEAM_NAME FROM TEAM
And for each team ID encountered executes
并且对于遇到的每个团队 ID 执行
SELECT PLAYER_NAME FROM PLAYER WHERE TEAM_ID = <PUT_TEAM_ID_RIGHT_HERE>
So this way you will not get duplicates references on one side
所以这样你就不会在一侧得到重复的引用
regards,
问候，
回答by Burhan Khalid Butt
select * from temptable
where rnum --unique key
in
(
SELECT RNUM --unique key
FROM temptable
WHERE ( HistoryStatus
) IN (SELECT HistoryStatus
FROM temptable
GROUP BY
HistoryStatus
HAVING COUNT(*) <= 1));
I have not tested this code. I have used similar code and it works. The syntax is in Oracle.
我没有测试过这段代码。我使用过类似的代码并且它有效。语法在 Oracle 中。
回答by ogniwo100
using namespaces and subqueries You can do it:
使用命名空间和子查询你可以这样做：
declare @data table (RequestID varchar(20), CreatedDate datetime, HistoryStatus varchar(20))
insert into @data values ('CF-0000001','8/26/2009 1:07:01 PM','For Review');
insert into @data values ('CF-0000001','8/26/2009 1:07:01 PM','Completed');
insert into @data values ('CF-0000112','8/26/2009 1:07:01 PM','For Review');
insert into @data values ('CF-0000113','8/26/2009 1:07:01 PM','For Review');
insert into @data values ('CF-0000114','8/26/2009 1:07:01 PM','Completed');
insert into @data values ('CF-0000115','8/26/2009 1:07:01 PM','Completed');
select d1.RequestID,d1.CreatedDate,d1.HistoryStatus
from @data d1
where d1.HistoryStatus = 'Completed'
union all
select d2.RequestID,d2.CreatedDate,d2.HistoryStatus
from @data d2
where d2.HistoryStatus = 'For Review'
and d2.RequestID not in (
select RequestID
from @data
where HistoryStatus = 'Completed'
and CreatedDate = d2.CreatedDate
)
Above query returns
以上查询返回
CF-0000001, 2009-08-26 13:07:01.000, Completed
CF-0000114, 2009-08-26 13:07:01.000, Completed
CF-0000115, 2009-08-26 13:07:01.000, Completed
CF-0000112, 2009-08-26 13:07:01.000, For Review
CF-0000113, 2009-08-26 13:07:01.000, For Review
回答by Charles Bretana
If this is a SQL question, and I understand what you are asking, (it's not entirely clear), just add distinct to the query
如果这是一个 SQL 问题，并且我理解你在问什么（它不完全清楚），只需在查询中添加 distinct
Select Distinct * From TempTable
回答by PRANEETH VARMA
try using select distinct x.* from ( your query)
尝试使用 select distinct x.* from ( your query)
Thanks.
谢谢。
回答by Naimisha Jogi
To fetch only one distinct record from duplicate column of two rows you can use "rowid" column which is maintained by oracle itself as Primary key,so first try
要从两行的重复列中仅获取一个不同的记录，您可以使用由 oracle 本身维护的“rowid”列作为主键，因此首先尝试
"select rowid,RequestID,CreatedDate,HistoryStatus from temptable;"
and then you can fetch second row only by it's value of 'rowid' column by using in SELECT statement.
然后您只能通过在 SELECT 语句中使用它的“rowid”列的值来获取第二行。
