Question

一人因他的朋友购买而获得10%的礼让。

表2

Reference table
Transaction table

参考

Person_id    Referrer_id
3             1
4             1
5             1
6             2

交易表

   Person_id   Amount     Action     Date
   3           100        Purchase   10-20-2011
   4           200        Purchase   10-21-2011
   6           400        Purchase   12-15-2011
   3           200        Purchase   12-30-2011
   1            50        Commision  01-01-2012
   1            10        Cm_Bonus   01-01-2012
   2            20        Commision  01-01-2012

• 如何获得下列转手结果——Person_id=1

Month     Ref_Pur  Earn_Comm   Todate_Earn_Comm   BonusRecvd  Paid   Due
10-2011    300      30          30                  0           0     30
11-2011      0       0          30                  0           0     30
12-2011    200      20          50                  0           0     50
01-2012      0       0          50                 10          50      0

上文使用的标签是:

Ref_Pur          = Total Referred Friend s Purchase for that month

Earn_Comm        = 10% Commision earned for that month

Todate_Earn_Comm = Total Running Commision earned upto that month

MYSQL CODE, i 写

SELECT dx1.month,
       dx1.ref_pur,
       dx1.earn_comm,
       ( @cum_earn := @cum_earn + dx1.earn_comm ) as todate_earn_comm

FROM

(
    select date_format(`date`, %Y-%m ) as month,
           sum(amount) as ref_pur , 
           (sum(amount)*0.1) as earn_comm
    from transaction tr, reference rf
    where tr.person_id=rf.person_id and
          tr.action= Purchase  and
          rf.referrer_id=1
    group by date_format(`date`, %Y-%m )
    order by date_format(`date`, %Y-%m )

)as dx1

JOIN (select @cum_earn:=0)e;

如何加入也包含不依赖参考表的BonusRecvd、Paid和“应尽人意”的问题?

and also generate row for the month 11-2011 , even though no trnx occured on that month

Answer 1

如果你想把佣金和奖金列入成果,你很可能需要将相应的词语(<>行动编码>,(......,Cm_Bonus )纳入你用来计算结果的初步数据集。或者,至少是这样说的,可能就是这样:

SELECT t.Amount, t.Action, t.Date
FROM Transaction t LEFT JOIN Reference r ON t.Person_id = r.Person_id
WHERE r.Referrer_id = 1 AND t.Action =  Purchase 
   OR t.Person_id   = 1 AND t.Action IN ( Commision ,  Cm_Bonus )

And when calculating monthly SUMs, you can use CASE expressions to distinguish among Amounts related to differnt types of Action. This is how the corresponding part of the query might look like:

…
IFNULL(SUM(CASE Action WHEN  Purchase   THEN Amount END)      , 0) AS Ref_Pur,
IFNULL(SUM(CASE Action WHEN  Purchase   THEN Amount END) * 0.1, 0) AS Earn_Comm,
IFNULL(SUM(CASE Action WHEN  Cm_Bonus   THEN Amount END)      , 0) AS BonusRecvd,
IFNULL(SUM(CASE Action WHEN  Commision  THEN Amount END)      , 0) AS Paid
…

在计算<代码>Due值时,你可以选择另一个变数,与@cum_earn相类似,但你也需在<代码>后再作改动。

(@cum_due := @cum_due + Earn_Comm - Paid) AS Due

最后一个问题似乎是几个月的缺失。为了解决这一问题,我将采取以下行动:

第一个和最后一个日期从次点开始处理(该职位开始时通过询问获得)。
每一日期的对应月份(即仅同一月的第一个日期)。
采用数字表编制一个月清单,涵盖上个步骤计算的两个月。
填补有待处理的子集中的几个月,并利用其余几个月在子集中添加婚礼交易。

如你所知,在采取这些步骤时,“待处理的文件”需要两次触及。因此,为了节约起见,我将在临时表格中插入这一次,并使用该表,而不是几次执行同样的(次)顺序。

第3步中提到的数字表是我建议始终保持手法的工具。如果你特赦 p子,你只需要一开始,而且如果你对你来说,其用途可能很多。下表仅以一个方式列出:

CREATE TABLE numbers (n int);
INSERT INTO numbers (n) SELECT 0;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
INSERT INTO numbers (n) SELECT cnt + n FROM numbers, (SELECT COUNT(*) AS cnt FROM numbers) s;
/* repeat as necessary; every repeated line doubles the number of rows */

看来就是这样。我不会在这里提出一个完整的解决办法,使你有机会在你热切希望的情况下,以自己的方式利用上述建议。但是,如果你在奋力或仅仅想证实其can>/em>将适用于所要求的效果,那么你可以尝试,这个文件是“采取行动”的。

友情链接