这条SQL语句 要怎么写？

Question

NO       content           address  username
41 1111111111 212 刘
42 wqwq          wqwq 刘
43 2121212          212121 刘
45 1212          212 刘
46 8888888888 88888888888 刘
46 8888888888 2121     刘
43 2121          21212    刘我要的结果是：
NO       content           address  username
41 1111111111 212 刘
42 wqwq          wqwq 刘
43 2121212          212121 刘
45 1212          212 刘
46 8888888888 88888888888 刘我已经写好：
SELECT a.Customer_NO,a.Customer_name Customer_name,b.Connect_Name,a.Customer_person FROM CM_Customers a left JOIN CM_Connect b 
ON a.Customer_NO = b.Connect_customerno 
WHERE a.Customer_person = '刘' 
后面要怎么过滤重复的记录？

Answer 1

;with t as
(
SELECT rn=ROW_NUMBER()over(partition by [username],[NO] order by [NO]),
 a.Customer_NO,a.Customer_name Customer_name,b.Connect_Name,a.Customer_person 
FROM CM_Customers a left JOIN CM_Connect b  
ON a.Customer_NO = b.Connect_customerno  
WHERE a.Customer_person = '刘'  
)
select * from t where rn=1

Answer 2

SELECT
 a.Customer_NO,a.Customer_name Customer_name,b.Connect_Name,a.Customer_person
FROM
 CM_Customers a 
left JOIN
 CM_Connect b  
ON
 a.Customer_NO = b.Connect_customerno  
WHERE
 a.Customer_person = '刘'  
and
 b.Connect_Name=(select min(Connect_Name) from CM_Connect where Connect_customerno=b.Connect_customerno)

Answer 3

SELECT 
    NO,max(content),max(address),username
FROM CM_Customers  
WHERE username = '刘'  
group by NO,username

Answer 4

;WITH T AS
(
SELECT RN=ROW_NUMBER()OVER(PARTITION BY [USERNAME],[NO] ORDER BY [NO]),
 A.CUSTOMER_NO,A.CUSTOMER_NAME CUSTOMER_NAME,B.CONNECT_NAME,A.CUSTOMER_PERSON 
FROM CM_CUSTOMERS A 
LEFT JOIN CM_CONNECT B  
ON A.CUSTOMER_NO = B.CONNECT_CUSTOMERNO  
WHERE A.CUSTOMER_PERSON = '刘'  
)
SELECT CUSTOMER_NO,CUSTOMER_NAME,CONNECT_NAME,CUSTOMER_PERSON 
FROM T 
WHERE RN=1

Answer 5

SELECT
 a.Customer_NO,a.Customer_name Customer_name,b.Connect_Name,a.Customer_person
FROM
 CM_Customers a 
left JOIN
 CM_Connect b  
ON
 a.Customer_NO = b.Connect_customerno  
WHERE
 a.Customer_person = '刘'  
and
 b.Connect_Name=(select max(Connect_Name) from CM_Connect where Connect_customerno=b.Connect_customerno)

Answer 6

--sql 2000
create table tb(NO int , content varchar(20),address varchar(20),username varchar(20))
insert into tb values(41 ,'1111111111' ,'212'         ,'刘')
insert into tb values(42 ,'wqwq'       ,'wqwq'        ,'刘')
insert into tb values(43 ,'2121212'    ,'212121'      ,'刘')
insert into tb values(45 ,'1212'       ,'212'         ,'刘')
insert into tb values(46 ,'8888888888' ,'88888888888' ,'刘')
insert into tb values(46 ,'8888888888' ,'2121'        ,'刘')
insert into tb values(43 ,'2121'       ,'21212'       ,'刘')
goselect NO ,content ,address ,username from
(
  select * , px = (select count(1) from tb where no = t.no and (content > t.content or (content = t.content and address > t.address))) + 1 from tb t
) m
where px = 1
order by nodrop table tb/*
NO          content              address              username             
----------- -------------------- -------------------- -------------------- 
41          1111111111           212                  刘
42          wqwq                 wqwq                 刘
43          2121212              212121               刘
45          1212                 212                  刘
46          8888888888           88888888888          刘（所影响的行数为 5 行）*/--sql 2005
create table tb(NO int , content varchar(20),address varchar(20),username nvarchar(20))
insert into tb values(41 ,'1111111111' ,'212'         ,N'刘')
insert into tb values(42 ,'wqwq'       ,'wqwq'        ,N'刘')
insert into tb values(43 ,'2121212'    ,'212121'      ,N'刘')
insert into tb values(45 ,'1212'       ,'212'         ,N'刘')
insert into tb values(46 ,'8888888888' ,'88888888888' ,N'刘')
insert into tb values(46 ,'8888888888' ,'2121'        ,N'刘')
insert into tb values(43 ,'2121'       ,'21212'       ,N'刘')
goselect NO ,content ,address ,username from
(
  select * , px = row_number() over(partition by no order by content desc , address desc) from tb t
) m
where px = 1
order by nodrop table tb/*
NO          content              address              username
----------- -------------------- -------------------- --------------------
41          1111111111           212                  刘
42          wqwq                 wqwq                 刘
43          2121212              212121               刘
45          1212                 212                  刘
46          8888888888           88888888888          刘(5 行受影响)
*/