oracle 如何使用 Distinct 从表中仅选择一条唯一记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3116982/
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 Select only one unique record from the Table using Distinct
提问by Nubkadiya
my table have several records which has the same MemberID. i want to result out only one record.
我的表有几个具有相同 MemberID 的记录。我只想得到一个记录。
select DISTINCT(MemberID) from AnnualFees;
then result will come. but i want to show the other column data also but when i do this
那么结果就会来。但我也想显示其他列数据，但是当我这样做时
select DISTINCT(MemberID),StartingDate,ExpiryDate,Amount from AnnualFees;
all the details including same MemberID data also displaying.
所有详细信息，包括相同的 MemberID 数据也显示。
can someone help me.
有人能帮我吗。
回答by Tony Andrews
Assuming you just want any row at random for each memberid than you can do this:
假设您只想要每个成员随机的任何行，那么您可以这样做：
select memberid, this, that, theother
from
(
select memberid, this, that, theother,
row_number() over (partition by memberid order by this) rn
from annualfees
)
where rn = 1;
If you wanted a specific row per memberid, e.g. the one with the most recent StartDate then you could modify it to:
如果您想要每个 memberid 的特定行，例如具有最新 StartDate 的行，那么您可以将其修改为：
select memberid, this, that, theother
from
(
select memberid, this, that, theother,
row_number() over (partition by memberid order by StartDate desc) rn
from annualfees
)
where rn = 1;
回答by MarkyBoyMark
don't know if this is quite what you need, but you may need to look at GROUP BY instead of DISTINCT...
不知道这是否正是您所需要的，但您可能需要查看 GROUP BY 而不是 DISTINCT ...
if you have several records with the same member id, you may need to specify exaclty how to identify the one you want from the others
如果您有多个具有相同成员 ID 的记录，您可能需要指定 exaclty 如何从其他记录中识别您想要的记录
eg to get each member`s last starting date:
例如获取每个成员的最后开始日期：
SELECT memberid, max(startingdate)
FROM annualfees
GROUP BY memberid
but if you need to identify one record in this kind of way but also display the other columns, i think you may need to do some trickery like this...
但是如果您需要以这种方式识别一条记录同时还需要显示其他列，我认为您可能需要像这样做一些诡计......
eg sub-query the above SELECT with a join to join the other columns you want:
例如，子查询上面的 SELECT 以加入你想要的其他列：
SELECT subq.memid, subq.startdate, a.expirydate, a.amount
FROM (
SELECT memberid AS memid, max(startingdate) AS startdate
FROM annualfees
GROUP BY memberid ) subq
INNER JOIN annualfees a ON a.memberid = subq.memid
AND a.startingdate = subq.startdate
from start to finish, also showing data table (o/p was traced/grabbed using "SET VERIFY ON")...
从头到尾，还显示了数据表（使用“SET VERIFY ON”跟踪/抓取o/p）...
-- show all rows
select *
from annualfees
order by memberid, startingdate
MEMBERID STARTINGDATE EXPIRYDATE AMOUNT
---------------------- ------------------------- -------------------- --------------------
1 02-DEC-09 05-FEB-10 111
1 25-JUN-10 25-JUN-11 222
2 25-APR-10 25-JUN-13 333
3 rows selected
/
-- show one member`s data using max(startingdate) as selector.
SELECT memberid, max(startingdate)
FROM annualfees
GROUP BY memberid
MEMBERID MAX(STARTINGDATE)
---------------------- -------------------------
1 25-JUN-10
2 25-APR-10
2 rows selected
/
-- show above data joined with the other columns.
SELECT subq.memid, subq.startdate, a.expirydate, a.amount
FROM (
SELECT memberid AS memid, max(startingdate) AS startdate
FROM annualfees
GROUP BY memberid ) subq
INNER JOIN annualfees a ON a.memberid = subq.memid AND a.startingdate = subq.startdate
MEMID STARTDATE EXPIRYDATE AMOUNT
---------------------- ------------------------- -------------------- --------------------
1 25-JUN-10 25-JUN-11 222
2 25-APR-10 25-JUN-13 333
2 rows selected
/
回答by Martin Smith
You need to select which of the rows with duplicate MemberIDs to return in some way. This will get the row with the greatest startingDate.
您需要选择以某种方式返回哪些具有重复 MemberID 的行。这将获得具有最大起始日期的行。
SELECT MemberID,StartingDate,ExpiryDate,Amount
FROM AnnualFees af
WHERE NOT EXISTS (
SELECT * from AnnualFees af2
WHERE af2.MemberID = af.MemberID
AND af2.StartingDate > af.StartingDate)
回答by KoolKabin
select DISTINCT MemberID,StartingDate,ExpiryDate,Amount from AnnualFees;
从年费中选择 DISTINCT MemberID,StartingDate,ExpiryDate,Amount;
remove paranthesis
去除括号
