sql语句请帮忙，急。。。在线等

Question

数据RoleWorkNo 这行有重复数据 保留FlowNO最大的这行，其他重复的数据不要，sql怎么改，谢谢大家！
SELECT A.TypeCode,A.TypeName,
B.FlowNo,B.RoleCode,
C.RoleName,C.RoleName_JP,C.OptAccess,
D.DeptId,D.RoleWorkNo,D.WorkNoGrant
FROM OA_FlowDefinition A,
 OA_WorkFlowDefinition B,
 OA_RolesName C,
 OA_OrgRoles D
WHERE A.TypeCode='p000'
and A.TypeCode=B.TypeCode
and B.RoleCode=C.RoleCode
and C.RoleCode=D.RoleCode
and DeptId=216
ORDER BY FlowNo ASC
sql

Answer 1

;
WITH    cte
          AS ( SELECT   A.TypeCode ,
                        A.TypeName ,
                        B.FlowNo ,
                        B.RoleCode ,
                        C.RoleName ,
                        C.RoleName_JP ,
                        C.OptAccess ,
                        D.DeptId ,
                        D.RoleWorkNo ,
                        D.WorkNoGrant
               FROM     OA_FlowDefinition A ,
                        OA_WorkFlowDefinition B ,
                        OA_RolesName C ,
                        OA_OrgRoles D
               WHERE    A.TypeCode = 'p000'
                        AND A.TypeCode = B.TypeCode
                        AND B.RoleCode = C.RoleCode
                        AND C.RoleCode = D.RoleCode
                        AND DeptId = 216
               ORDER BY FlowNo ASC
             )
    SELECT  *
    FROM    cte a
    WHERE   EXISTS ( SELECT 1
                     FROM   ( SELECT    RoleWorkNo ,
                                        MAX(FlowNO) FlowNO
                              FROM      cte
                              GROUP BY  RoleWorkNo
                            ) b
                     WHERE  a.RoleWorkNo = b.RoleWorkNo
                            AND a.FlowNO = b.FlowNO )

Answer 2

列数依旧要  谢谢您， 您的代码是不是没贴全   
;
WITH    cte 这个是什么意思啊？麻烦您了

Answer 3

---把你得到这个结果集保存在一张临时表中#tb中；
SELECT A.TypeCode,A.TypeName,
                B.FlowNo,B.RoleCode,
                C.RoleName,C.RoleName_JP,C.OptAccess,
                D.DeptId,D.RoleWorkNo,D.WorkNoGrant
        FROM OA_FlowDefinition A,
             OA_WorkFlowDefinition B,
             OA_RolesName C,
             OA_OrgRoles D
        WHERE A.TypeCode='p000'
        and A.TypeCode=B.TypeCode
        and B.RoleCode=C.RoleCode
        and C.RoleCode=D.RoleCode
        and DeptId=216
        ORDER BY FlowNo ASC
----查询#tb表
Select t1.*
From #tb as t1
Where not Exists (
Select *
From #tb as t2
Where t1.RoleWorkNo = t2.RoleWorkNo
And t1.FlowNo < t2.FlowNo
)

Answer 4

try this,select TypeCode,TypeName,FlowNo,RoleCode,RoleName,RoleName_JP,
       OptAccess,DeptId,RoleWorkNo,WorkNoGrant
from
(SELECT A.TypeCode,
        A.TypeName,
        B.FlowNo,
        B.RoleCode,
        C.RoleName,
        C.RoleName_JP,
        C.OptAccess,
        D.DeptId,
        D.RoleWorkNo,
        D.WorkNoGrant,
        row_number() over(partition by RoleWorkNo order by FlowNo desc) 'rn'
 FROM OA_FlowDefinition A,
      OA_WorkFlowDefinition B,
      OA_RolesName C,
      OA_OrgRoles D
 WHERE A.TypeCode='p000'
 and A.TypeCode=B.TypeCode
 and B.RoleCode=C.RoleCode
 and C.RoleCode=D.RoleCode
 and DeptId=216) t
where t.rn=1