我正在寻找一种高效的方法,以从我的SELECT语句中排除行,其中某个列返回了多个具有相同值的行。具体而言,我正在选择一堆帐户,但需要排除具有相同SSN关联的多个帐户。
这将返回所有只有一行的社会安全号码(SSN)
select ssn,count(*)
from SomeTable
group by ssn
having count(*) = 1
这将返回所有行数大于1的SSN。
select ssn,count(*)
from SomeTable
group by ssn
having count(*) > 1
完整查询应该像这样(适用于 SQL Server 7 及以上版本)
select a.* from account a
join(
select ssn
from SomeTable
group by ssn
having count(*) = 1) s on a.ssn = s.ssn
having count(*) > 1
。 - triclosan对于 SQL 2005 或更高版本,您可以尝试以下方法:
WITH qry AS
(
SELECT a.*,
COUNT(*) OVER(PARTITION BY ssn) dup_count
FROM accounts a
)
SELECT *
FROM qry
WHERE dup_count = 1
对于SQL 2000和7:
SELECT a.*
FROM accounts a INNER JOIN
(
SELECT ssn
FROM accounts b
GROUP BY ssn
HAVING COUNT(1) = 1
) b ON a.ssn = b.ssn
SELECT *
FROM #Temp
WHERE SSN NOT IN (SELECT ssn FROM #Temp GROUP BY ssn HAVING COUNT(ssn) > 1)
感谢大家提供的详细建议。说到底,我需要使用一个相关子查询。基本上,我需要做的是:
SELECT acn, ssn, [date] FROM Account a
WHERE NOT EXISTS (SELECT 1 FROM Account WHERE ssn = a.ssn AND [date] < a.[date])
希望这能帮助到某个人。
我从未更新过这个...
在我的最终提交中,我通过左连接来提高效率(相关子查询不可接受,因为它需要花费大量时间来检查每个记录是否与其他150K个记录匹配)。以下是解决我的问题所必须要做的:
SELECT acn, ssn
FROM Account a
LEFT JOIN (SELECT ssn, COUNT(1) AS counter FROM Account
GROUP BY ssn) AS counters
ON a.ssn = counters.ssn
WHERE counter IS NULL OR counter = 0