T-SQL 经典查询

1.有重复，并且某字段值最最大的数据

USERCODE有重复，查找ROLECODE值最大值的记录。

select USERCODE,CREATEUSER,ROLECODE
from SCM_ROLEANDUSER  a  
where  ROLECODE=(select max(b.ROLECODE) 
               from SCM_ROLEANDUSER b 
                where a.USERCODE = b.USERCODE )
AND EXISTS(SELECT USERCODE FROM SCM_ROLEANDUSER C WHERE C.USERCODE=A.USERCODE GROUP BY C.CREATEUSER HAVING COUNT(*)>1)