md_YOS/src/custom/restcontroller/webmanage/sale/serviceorder/SQL/序列号查询.sql

select * from ((SELECT
                    t4.agentnum,
                    t5.contact,
                    t5.phonenumber,
                    t5.address,
                    case when ifnull(t6.cardno,'')='' then '未安装' else '已安装' end isinstalled,
                    t6.cardno,
                    t6.begdate,
                    t6.enddate,
                    t6.isvoid,
                    t7.sa_customersid,
                    t1.sku,
                    t1.sa_itemskuid,
                    t2.itemid,
                    t2.itemno,
                    t2.itemname,
                    t2.model,
                    t2.spec,
                    t3.unitname,
                    t7.name as customername,
                    t7.phonenumber as customerphonenumber
                FROM sa_itemsku t1
                         LEFT JOIN plm_item t2 ON t1.itemid = t2.itemid AND t1.siteid = t2.siteid
                         LEFT JOIN plm_unit t3 ON t3.unitid = t2.unitid AND t3.siteid = t2.siteid
                         LEFT JOIN sa_agents t4 ON t4.sys_enterpriseid = t1.sys_enterpriseid
                    AND t4.siteid = t1.siteid
                         LEFT JOIN sys_enterprise t5 ON t5.sys_enterpriseid = t4.sys_enterpriseid
                    AND t5.siteid = t4.siteid
                         LEFT JOIN sa_warrantycard t6 ON t6.sku = t1.sku AND t1.siteid = t6.siteid
                         LEFT JOIN sa_customers t7 ON t6.sa_customersid = t7.sa_customersid
                    AND t6.siteid = t7.siteid
                WHERE t1.siteid =$siteid$
                  AND t1.sys_enterpriseid = $sys_enterpriseid$
                ORDER BY t1.sa_itemskuid)

               UNION ALL

               (SELECT
                    t4.agentnum,
                    t5.contact,
                    t5.phonenumber,
                    t5.address,
                    case when ifnull(t6.cardno,'')='' then '未安装' else '已安装' end isinstalled,
                    t6.cardno,
                    t6.begdate,
                    t6.enddate,
                    t6.isvoid,
                    t7.sa_customersid,
                    t1.sku,
                    t1.sa_itemskuid,
                    t2.itemid,
                    t2.itemno,
                    t2.itemname,
                    t2.model,
                    t2.spec,
                    t3.unitname,
                    t7.name as customername,
                    t7.phonenumber as customerphonenumber
                FROM sa_itemsku t1
                         INNER JOIN sa_agents a ON t1.sa_agentsid = a.sa_agentsid
                    AND t1.siteid = a.siteid
                         LEFT JOIN plm_item t2 ON t1.itemid = t2.itemid AND t1.siteid = t2.siteid
                         LEFT JOIN plm_unit t3 ON t3.unitid = t2.unitid AND t3.siteid = t2.siteid
                         LEFT JOIN sa_agents t4 ON t4.sys_enterpriseid = t1.sys_enterpriseid
                    AND t4.siteid = t1.siteid
                         LEFT JOIN sys_enterprise t5 ON t5.sys_enterpriseid = t4.sys_enterpriseid
                    AND t5.siteid = t4.siteid
                         LEFT JOIN sa_warrantycard t6 ON t6.sku = t1.sku AND t1.siteid = t6.siteid
                         LEFT JOIN sa_customers t7 ON t6.sa_customersid = t7.sa_customersid
                    AND t6.siteid = t7.siteid
                WHERE t1.siteid = $siteid$
                  AND a.sys_enterpriseid =$sys_enterpriseid$
                  AND t1.sys_enterpriseid != $sys_enterpriseid$
                ORDER BY t1.sa_itemskuid)) t1 where $where$