超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