php - MySQL View table for referrals？

Question

我有一个包含以下信息的表(用户):

id  |  user_id |  name    |  country    | referral_id
----------------------------------------------------
1   |  10      |  Jhon    |  India      | 0
2   |  11      |  Krish   |  America    | 0
3   |  12      |  Boss    |  Canada     | 0
4   |  13      |  Jack    |  India      | 11
5   |  14      |  Ronald  |  Japan      | 10
6   |  15      |  Jey     |  Germany    | 10

还有其他表 (total_earning)，其中包含以下信息:

id  |  user_id  |  date          | earning
--------------------------------------------
1   |  10       |  2015-10-25    | 4.4$
2   |  14       |  2015-10-25    | 2.2$
3   |  15       |  2015-10-27    | 3.0$
4   |  15       |  2015-10-25    | 1.5$

我想向用户支付推荐费(该用户收入的 10%)。

例如。 User.10 有 2 个推荐(User.14 和 User.15)，所以我想给 user.14 和用户 10%。 15 收入。

如何从这 2 个表创建 MySQL View 表？

Answer 1

您需要一个由两部分组成的查询。第一个查询是基于每个用户的任何可能收入。这是“JustMe”的别名……每个人直接赚取的收入。

由此，对第二个查询“Referrals”别名进行 LEFT-JOIN(因为并非所有用户都有来自其他用户的推荐)。这会将收入加入到用户表中，并从 USERS 表中获取推荐 ID 作为分组。您可以让 10 个人都被用户 X 推荐，因此您希望将他们的所有收入汇总到用户 X 的收入中。

从那里，然后连接到用户表以拉出主要人物及其收入以及一列以显示他们将收到的推荐奖金总额。

select
      U.Name,
      U.Country,
      JustMe.JustMyEarnings,
      coalesce( Referrals.ReferralEarnings * .1, 0 ) as ReferralBonus
from
   ( select
           te.user_id,
           sum( te.earning ) justMyEarnings
        from
           total_earning te
        group by
           te.user_id ) JustMe
   LEFT JOIN
   ( select
           U2.referral_id,
           sum( te2.earning ) as ReferralEarnings
        from
           total_earnings te2
              join users U2
                 on te2.user_id = U2.user_id
                AND U2.referral_id > 0
        group by
           U2.referral_id ) Referrals
      on JustMe.user_id = Referrals.referral_id
   JOIN Users U
      on JustMe.user_id = U.user_id

并且，如果您希望将其作为 View ，只需

CREATE VIEW UserReferralView as
(entire select from above...)

但是，如果您想要单个用户的结果，那么我会进行调整，因为您不希望每次需要一个人的总计时内部查询都查询整个表。我会将内部查询调整为类似

   ( select
           te.user_id,
           sum( te.earning ) justMyEarnings
        from
           total_earning te
        where
           te.user_id = THE_ONE_USERID_YOU_WANT
        group by
           te.user_id ) JustMe
   LEFT JOIN
   ( select
           U2.referral_id,
           sum( te2.earning ) as ReferralEarnings
        from
           total_earnings te2
              join users U2
                 on te2.user_id = U2.user_id
                AND U2.referral_id > 0
                AND te2.referral_id = THE_ONE_USERID_YOU_WANT
        group by
           U2.referral_id ) Referrals