SQL Server Query statement filters duplicate data

Case one: There is a complete duplicate of the data in the table, that is, all field contents are the same

CREATE TABLE #

(User ID int, name varchar (10), age int)

INSERT INTO #

Select 111, ' John ', UNION ALL

Select 222, ' Dick ', UNION ALL

Select 333, ' Harry ', UNION All

Select 111, ' John ', 26

Method: SELECT DISTINCT * from #

Scenario 2: There are partial data duplicates in the table, where at least one field in the duplicate data does not repeat the CREATE TABLE # (user ID int, name varchar (10), age int, date datetime) insert INTO #select 111, ' John ', 2010-02-23 Union allselect 222, ' Dick ', 2010-03-13 Union Allselect 333, ' Harry ', 2011-03-25 Union allselect 111, ' John ', 26 2011-07-07 Method:--When two heavy, take the date of the big one

SELECT * FROM t a

Where NOT EXISTS (select 1 from t where a. User id= user id A. Name = name and date >a. Date)