需求

• A 邀请 B, B 邀请 C，统计 A 的会员数。
• B 下单的总数
• 默认排序按用户注册时间，支持按会员数降序、会员下单数降序排序。

表结构

# user 表
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| dateline    | int          | YES  |     | NULL    |                |
| inviter_uid | int          | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
# user_order 表
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int unsigned | NO   | PRI | NULL    | auto_increment |
| user_id  | int unsigned | YES  |     | NULL    |                |
| name     | varchar(255) | YES  |     | NULL    |                |
| dateline | int          | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

实现

<?php
namespace app\index\controller;
use think\Controller;
use Db;

class Index
{
    protected static $mapping = ['time' => 'dateline', 'member' => 'memberCount', 'order' => 'orderCount'];

    public function hello()
    {
        // 处理 sort 排序参数
        $sort = request()->param('sort', 'time');
        $sorts = array_keys(self::$mapping);
        if (!in_array($sort, $sorts)) {
            die('参数不合法');
        }
        $sort = self::$mapping[$sort];

        $model = Db();
        $users = $model->query("SELECT
            u.id,
            u.dateline,
            DATE_FORMAT(FROM_UNIXTIME(u.dateline), '%Y-%m-%d %H:%i:%s') time,
            u.NAME,
            ( SELECT count( o.id ) FROM user_order o WHERE o.user_id = u.id ) 'orderCount',
            (
            SELECT
                count( uu.id ) 
            FROM
                USER uu 
            WHERE
                (
                uu.inviter_uid IN ( SELECT uuu.id FROM USER uuu WHERE uuu.inviter_uid = u.id )) 
                OR uu.inviter_uid = u.id 
            ) 'memberCount' 
        FROM
            USER u order by $sort DESC");
        dump($users);
    }
}

测试

浏览器访问

http://127.0.0.1:8083/public/index.php?s=index/index/hello

http://127.0.0.1:8083/public/index.php?s=index/index/hello&sort=time

http://127.0.0.1:8083/public/index.php?s=index/index/hello&sort=order

http://127.0.0.1:8083/public/index.php?s=index/index/hello&sort=member