jindiguanli_Code/sql/分配人员明细表查询.sql

SELECT
    person.fname_l2,
    person.fnumber,
    budDep.fname_l2,
    project.fname_l2,
    amountData.total
FROM
    (
        SELECT
            bDPAPD.CFPERSONID,
            bDP.CFBusDepID,
            bDP.CFProjectID,
            sum(bDPAPD.CFApprovalAmount) as total
        FROM
            CT_PB_BonusCycle bonusCycle
            inner JOIN "CT_PB_BonusCycleBusDepProject" "bDP" ON "bDP"."FPARENTID" = "bonusCycle"."FID"
            inner JOIN "CT_PB_BonusCAPD" "bDPAPD" ON "bDPAPD"."FPARENTID" = "bDP"."FID"
        WHERE
            bonusCycle.fid in (?)
            and bDP.cfstate = 2
        group by
            bDPAPD.CFPERSONID,
            bDP.CFProjectID,
            bDP.CFBusDepID
    ) amountData
    inner JOIN "t_org_admin" "budDep" ON "budDep"."fid" = amountData."CFBusDepID"
    LEFT JOIN "t_org_admin" "project" ON "project"."fid" = amountData."CFProjectID"
    inner JOIN "T_BD_PERSON" "person" ON amountData."CFPERSONID" = "person"."FID"