抽丝剥茧分解业绩

-- 获得会员点数表
DROP TABLE HYDS;

    SELECT fBH,SUM(DBO.fAppGetPoint(fBH,fJHSJ)) DS INTO HYDS
    FROM tAppMember
    WHERE 1=1 
    AND fJHSJ>='2015-12-15'
    AND fJHSJ<='2016-06-14'
    GROUP BY fBH

SELECT * FROM HYDS;

-- 获得父亲业绩表
DROP TABLE HYSCORE;
SELECT A.fFather,A.fArea,SUM(B.DS) DS INTO HYSCORE FROM tAppFZB A, HYDS B WHERE A.fChild = B.fBH GROUP BY A.fFather,A.fArea ORDER BY DS DESC;
SELECT * FROM HYSCORE

-- 获得20万业绩表
DROP TABLE HY100;
SELECT * INTO HY100 FROM HYSCORE WHERE DS>=100 ORDER BY DS DESC;
SELECT * FROM HY100;

-- 获得会员业绩转置表
DROP TABLE HYYJ;
  SELECT fFather,
  SUM(CASE fArea WHEN 'A区' THEN DS*2000 END) AS AQ,
  SUM(CASE fArea WHEN 'B区' THEN DS*2000 END) AS BQ
  INTO HYYJ
  FROM HY100 GROUP BY fFather

  DELETE FROM HYYJ WHERE AQ IS NULL;
  DELETE FROM HYYJ WHERE BQ IS NULL;
SELECT * FROM HYYJ;

-- 查询结果
SELECT fFather,AQ,BQ,DBO.fGetXM(fFather) XM FROM HYYJ ORDER BY AQ DESC

-- 验证一个人
SELECT * FROM tAppMember WHERE fBH IN (SELECT fChild FROM tAppFZB WHERE fFather='CN688555')

DECLARE @Father VARCHAR(20);
SET @Father = 'CN688555'
SELECT 

  DBO.fGetArea(fBH,@Father) fArea,Count(*) 人数,
  SUM(DBO.fAppGetPoint(fBH,fJHSJ)) 点数,
  SUM(DBO.fAppGetPoint(fBH,fJHSJ))*2000 金额
FROM tAppMember 
WHERE 1=1
  AND fJHSJ>='2015-12-15'
  AND fJHSJ<='2016-06-14'
  AND fBH IN (SELECT fChild FROM tAppFZB WHERE fFather = @Father) 
GROUP BY DBO.fGetArea(fBH,@Father)