php - 计算 id，插入，然后在同一个表 mysql 的另一列中按数字范围更新

Question

我正在尝试像这样更新数据库中的级别:“从用户表中计算(id)，其中 referral_id = id As aggref set agg_referral = aggref AND level = 1 if aggref 在 1 到 3 之间”到目前为止我知道但没有成功:(

这是我的数据库结构用户表:

  id (AI)      name       Referral id      agg_referral   Level
=========== ========== ================== ============== ======
     1        user A          0                 0          0
     2        user B          3                 0          0
     3        user C          1                 0          0
     4        user D          3                 0          0

这是我使用 cron 作业运行的代码:

  <?php
    include( $_SERVER['DOCUMENT_ROOT'] . '/config.php' );
    mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME);
    $query="UPDATE users SET agg_referral = (SELECT COUNT(id) from users WHERE users.referral_id = users.id)";
    mysql_query($query);

    ?>

I am expecting results like this

 id (AI)      name       Referral id      agg_referral   Level
=========== ========== ================== ============== ======
     1        user A          0                 0          0
     2        user B          3                 0          0
     3        user C          1                 2          1
     4        user D          3                 0          0

Answer 1

您可以使用更新连接:

update users u1
join (
    select referral_id,
        count(*) cnt
    from users
    group by referral_id
    ) u2 on u1.id = u2.referral_id    
set u1.agg_referral = u2.cnt,
    u1.level = case 
        when cnt < 1
            then 0
        else ceil((- 5 + sqrt(25 + 8 * cnt)) / 2)
        end;

Demo

它找到一个 id 被引用的次数，然后将它与表连接起来以更新计数和级别。

case 语句为 cnt = 0 或更小生成 0，为 1 和 3 之间的 cnt 生成 1，为 4 和 7 之间的 cnt 生成 2 以及 . .等等。

要了解有关公式如何工作的更多信息，请参阅 https://math.stackexchange.com/questions/2171745/find-group-number-for-a-given-number-from-groups-of-increasing-size

编辑:

如果您的初始组大小不是 3，请使用它来获得所需的等式:

ceil((1 - 2 * g + sqrt(4 * g * g + 1 - 4 * g + 8 * cnt)) / 2)

其中 g 是组的初始大小。

对于 g = 3，它求解为:

ceil( -5 + sqrt(25 + 8 * cnt) / 2 )

对于 g = 10，它求解为:

ceil( -19 + sqrt(361 + 8 * cnt) / 2 )

关于这个函数是如何实现的，请看上面提供的链接。