如何抑制或隐藏SQL中的重复值?

I've been looking around for a bit and have not been able to find out how to do this yet (though have found alot about analyzing it's performance?!)I want to perform a select which returns a few columns of data, and then also a nested table of related rows from another table (actually the same table joined on itself but I 'think' this is irelevant).So the data is something like this:id name registered1 Dan N2 Bill N3 Bob N4 Dan N5 Bill Y6 Dan YThe idea is perform a select which finds all the unregistered people who could be related to a registered account.So the result would look likeregistered.id name notreg.id name 5 Bill 2 Bill 6 Dan 1 Dan 4 DanI alright with SQL can handle all the selection criteria etc, and have a query that returns a normal inner join that finds this, but want to know if it's possible to get a result set kind of like this, so no repeated values on the left side?? 解决方案 You're probably better off suppressing duplicates it in the Client (for example in Jasper Reports uncheck Print Repeated Value or in the XML set isPrintRepeatedValues="false")However on anything that supports WITH and ROW_NUMBER() (e.g. Oracle, SQL Server 2005+) . WITH ns AS (SELECT Row_number() OVER (PARTITION BY name ORDER BY id) rn, id, name, registered FROM t WHERE registered = 'N') SELECT t.id, t.name, ns.id, ns.name FROM ns LEFT JOIN t ON ns.name = t.name AND t.registered = 'Y' AND ns.rn = 1 WHERE ns.name IN (SELECT name FROM t WHERE registered = 'Y') ORDER BY ns.name See working example If you don't have WITH and ROW_NUIMBER you can do thisSELECT t.id, t.name, ns.id, ns.name FROM t ns LEFT JOIN (SELECT MIN(id) id, name FROM t WHERE registered = 'N' GROUP BY name) MINNS ON ns.id = MINNS.id LEFT JOIN t ON ns.name = t.name AND t.registered = 'Y' AND MINNS.id IS NOT NULL WHERE ns.registered = 'N' AND ns.name IN (SELECT name FROM t WHERE registered = 'Y') ORDER BY ns.name, ns.idSee working example 这篇关于如何抑制或隐藏SQL中的重复值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！