md_YOS/src/custom/restcontroller/webmanage/sale/serviceorder/SQL/服务申请单列表查询.sql

SELECT
    t1.*,
    t2.enterprisename,
    t2.abbreviation,
    t2.phonenumber enterprisephonenumber,
    t3.agentnum,
    t4.enterprisename serviceenterprisename,
    t4.abbreviation serviceabbreviation,
    t4.phonenumber servicephonenumber,
    t5.agentnum serviceagentnum,
    t7.name saler_name,
    t8.itemid,
    t8.sku,
    t8.cardno,
    t8.warrantystatus,
    t9.itemno,
    t9.itemname,
    t9.model,
    t9.spec,
    t12.sys_enterpriseid sys_enterpriseid_initiate,
    t12.agentnum agentnum_initiate,
    t12.enterprisename enterprisename_initiate,
    t12.abbreviation abbreviation_initiate
FROM
    sa_serviceorder t1
        LEFT JOIN sys_enterprise t2 ON t1.sys_enterpriseid = t2.sys_enterpriseid
        AND t1.siteid = t2.siteid
        LEFT JOIN sa_agents t3 ON t2.sys_enterpriseid = t3.sys_enterpriseid
        AND t2.siteid = t3.siteid
        LEFT JOIN sys_enterprise t4 ON t1.sys_enterpriseid_service = t4.sys_enterpriseid
        AND t1.siteid = t4.siteid
        LEFT JOIN sa_agents t5 ON t4.sys_enterpriseid = t5.sys_enterpriseid
        AND t4.siteid = t5.siteid
        left join sys_hr t7 on t7.hrid=t1.saler_hrid and t7.siteid=t1.siteid
        left join (SELECT * FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY sa_serviceorderid ORDER BY sa_serviceorderitemsid ASC) AS rowno FROM sa_serviceorderitems) t WHERE rowno = 1) t8 on t8.sa_serviceorderid=t1.sa_serviceorderid and t8.siteid=t1.siteid
        left join plm_item t9 on t9.itemid=t8.itemid and t9.siteid=t8.siteid
        left join sa_workorder t10 on t10.sa_serviceorderid=t1.sa_serviceorderid and t10.siteid=t1.siteid and t10.status !='作废'
        left join sys_enterprise_hr t11 on t11.userid=t1.createuserid  AND t11.siteid = t1.siteid
        left join sv_agents t12 on t12.sys_enterpriseid=t11.sys_enterpriseid  AND t12.siteid = t11.siteid
where t1.siteid=$siteid$ and $where$