超30万订单统计

select * from (   select               c.fname as fbmname ,             d.fname as fempname ,              a.FBILLNO AS FORBILLNO,              e.fname as fcustname ,            e.fnumber as fcustnumber ,             sum(b.FBILLALLAMOUNT_LC)*1/10000 AS Foramount ,              case when LEFT(RIGHT(E.FNUMBER,8),4)>='2024' then '新客户' else '老客户' end as MK            from T_SAL_ORDER a            left join T_SAL_ORDERFIN b on b.fid=a.fid            left join T_BD_DEPARTMENT_L c on c.fdeptid=a.fsaledeptid            left join V_BD_SALESMAN_L d on d.fid=a.fsalerid            left join (    select b.fname ,                                         a.fnumber ,                                         a.FCUSTID ,                                         a.FCreateDate                             from T_BD_CUSTOMER a                           left join T_BD_CUSTOMER_L B ON B.Fcustid=A.Fcustid ) e on e.fcustid=a.fcustid           where a.FBILLNO like '%CO%' and e.FNUMBER like '%A%' group by c.fname , d.fname , a.FBILLNO , e.fname , e.FNUMBER ) AS G WHERE Foramount > '30'     ----主要是CLOUD中不允许使用DROP函数： ----所以对原来的报表的进行化化的结果   SELECT c.fname AS fbmname, d.fname AS fempname, a.FBILLNO AS FORBILLNO, e.fname AS fcustname, e.fnumber AS fcustnumber, SUM(b.FBILLALLAMOUNT_LC) / 10000 AS Foramount, CASE WHEN SUBSTRING(e.fnumber, 9, 4) >= '2024' THEN '新客户' ELSE '老客户' END AS MK FROM T_SAL_ORDER a LEFT JOIN T_SAL_ORDERFIN b ON b.fid = a.fid LEFT JOIN T_BD_DEPARTMENT_L c ON c.fdeptid = a.fsaledeptid LEFT JOIN V_BD_SALESMAN_L d ON d.fid = a.fsalerid LEFT JOIN (SELECT b.fname, a.fnumber, a.FCUSTID FROM T_BD_CUSTOMER a LEFT JOIN T_BD_CUSTOMER_L b ON b.Fcustid = A.Fcustid) e ON e.fcustid = a.fcustid WHERE a.FBILLNO LIKE '%CO%' AND e.FNUMBER LIKE '%A%' GROUP BY c.fname, d.fname, a.FBILLNO, e.fname, e.fnumber HAVING SUM(b.FBILLALLAMOUNT_LC) / 10000 > 30