SELECT DISTINCT p.sID AS sID, p.SNAME AS sName, mainOrg.sFName AS sFName
FROM SA_OPPerson p JOIN SA_OPOrg mainOrg ON p.SMAINORGID = mainOrg.sID
WHERE ( p.sID IN ('-') ) OR (p.sID IN (SELECT pm.SPERSONID AS sPersonID
FROM SA_OPOrg org JOIN SA_OPOrg orgC ON orgC.sFID LIKE org.sFID || '%' JOIN SA_OPOrg pm ON orgC.sID = pm.sParent
WHERE org.sID IN ('-', 'E93A43058AD540C191CEDC9B6C79E940', 'PSN01', 'A3226FBBBB864D92A5FBE925572BBA06') ))
ORDER BY mainOrg.sFName ASC, p.SNAME ASC
FROM SA_OPPerson p JOIN SA_OPOrg mainOrg ON p.SMAINORGID = mainOrg.sID
WHERE ( p.sID IN ('-') ) OR (p.sID IN (SELECT pm.SPERSONID AS sPersonID
FROM SA_OPOrg org JOIN SA_OPOrg orgC ON orgC.sFID LIKE org.sFID || '%' JOIN SA_OPOrg pm ON orgC.sID = pm.sParent
WHERE org.sID IN ('-', 'E93A43058AD540C191CEDC9B6C79E940', 'PSN01', 'A3226FBBBB864D92A5FBE925572BBA06') ))
ORDER BY mainOrg.sFName ASC, p.SNAME ASC
FROM SA_OPPerson p
JOIN SA_OPOrg mainOrg ON p.SMAINORGID = mainOrg.sID
WHERE p.sID IN(SELECT pm.SPERSONID AS sPersonID
FROM SA_OPOrg org
JOIN SA_OPOrg orgC ON orgC.sFID LIKE org.sFID || '%'
JOIN SA_OPOrg pm ON orgC.sID = pm.sParent
WHERE org.sID IN ('-', 'E93A43058AD540C191CEDC9B6C79E940', 'PSN01',
'A3226FBBBB864D92A5FBE925572BBA06')
union all
select '-' sPersonID from dual)
group by p.sID, p.SNAME, mainOrg.sFName
ORDER BY mainOrg.sFName ASC, p.SNAME ASC
2 把or语句去掉,中间用union all连接两个结果集
3 第二个语句中,将表与表关联的方法把in去掉
on是条件也能去掉吗?
不用in用什么?
String sqlGetPersons = "Select distinct p as sID, p.sName, mainOrg.sFName "
+ " from SA_OPPerson p "
+ " join SA_OPOrg mainOrg on p.sMainOrgID = mainOrg "
+ " OPTIONAL JOIN SA_OPOrg orgC ON p = orgC.sPersonID "
+ " OPTIONAL JOIN SA_OPOrg org ON org IN ("+orgIDs+") and orgC.sFID LIKE concat(org.sFID, '%') "
+ " where p in ("+psnIDs+") OR org is not null "
+ " order by mainOrg.sFName, p.sName ";
排序去掉ASC就好了