SQL：不显示重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14219319/
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
SQL: Don't show duplicates
提问by numberwang
I want to notshow duplicate rows in output for the sql below and so I have 'distinct' in there, but this doesn't work. I get output like:
我想不显示在输出重复的行对于下面的SQL，所以我在那里有“独立的”，但这并不工作。我得到如下输出：
PermitNumber PermitName CreatedOn
111 ABCD 1/2/2011
111 ABCD 3/4/2012
222 DFES 3/6/2000
and I want only one row with 111 but I get more than 1 row because 111 has more than one modification, but I don't care if it has 1 or 1000.
我只想要 111 行，但我得到 1 行以上，因为 111 有不止一个修改，但我不在乎它是 1 还是 1000。
select distinct (dbo.PermitNumber(mp.PermitId)),
dbo.PermitName(mp.PermitId),
mod.[CreatedOn] as [CreatedOn]
from tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
order by 1
Using SQL Server
使用 SQL Server
回答by Taryn
Distinct applies to all columns so you could use an aggregate function:
Distinct 适用于所有列，因此您可以使用聚合函数：
select mp.PermitNumber,
mp.PermitName,
max(mod.[CreatedOn])as [CreatedOn]
from tblPermit mp
Inner join dbo.[tblModification] mod
on mod.PermitId = mp.PermitId
Group by mp.PermitNumber,
mp.PermitName
order by 1
回答by MicSim
Use GROUP BY(exact syntax depending on your dbms):
使用GROUP BY（具体语法取决于您的 dbms）：
select (dbo.PermitNumber(mp.PermitId)),
dbo.PermitName(mp.PermitId),
min(mod.[CreatedOn] as [CreatedOn])
from tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
group by (dbo.PermitNumber(mp.PermitId)), dbo.PermitName(mp.PermitId)
order by 1
回答by John Woo
;WITH latestRecord
AS
(
SELECT PermitNumber, PermitName, CreatedOn,
ROW_NUMBER() OVER (PARTITION BY PermitNumber, PermitName
ORDER BY CreatedOn DESC) rn
FROM tblPermit
)
SELECT PermitNumber, PermitName, CreatedOn
FROM latestRecord
WHERE rn = 1
回答by Elastep
Than you should decide how to deal with a creation date. For example to show maximum of these days. Distinct is applied to all fields in the select clause.
比您应该决定如何处理创建日期。例如显示这些天的最大值。Distinct 应用于 select 子句中的所有字段。
select distinct (dbo.PermitNumber(mp.PermitId)),
dbo.PermitName(mp.PermitId)
from tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
order by 1
This will work but without created date
这将工作，但没有创建日期
回答by KM.
based on the limited info in the question try:
根据问题中的有限信息尝试：
select distinct (dbo.PermitNumber(mp.PermitId)),
dbo.PermitName(mp.PermitId),
MIN(mod.[CreatedOn]) as [CreatedOn]
--^^^new
from tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
group by (dbo.PermitNumber(mp.PermitId)), ---<<new
dbo.PermitName(mp.PermitId), ---<<new
order by 1
you need to do something with all the mod.[CreatedOn])values for each row, so group them together and display the MIN of each.
您需要mod.[CreatedOn])对每一行的所有值进行处理，因此将它们组合在一起并显示每个值的最小值。
