TP5,MySQL如何实现无限级查询统计

浏览:714 发布日期:2019/03/21 分类:求助交流 关键字: tp5 ThinkPHP MySQL 无限级 查询统计 layui
TP5,MySQL如何实现无限级查询统计
---------
场景:
有一个用户表,表内人员是无限级关系,
有一个销售表,表内是人员的销售流水记录
数据表如下:

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`pid` int(10) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '0', 'aaa');
INSERT INTO `user` VALUES ('2', '0', 'bbb');
INSERT INTO `user` VALUES ('3', '0', 'ccc');
INSERT INTO `user` VALUES ('4', '1', 'ddd');
INSERT INTO `user` VALUES ('5', '2', 'eee');
INSERT INTO `user` VALUES ('6', '1', 'fff');
INSERT INTO `user` VALUES ('7', '1', 'ggg');
INSERT INTO `user` VALUES ('8', '4', 'hhh');
INSERT INTO `user` VALUES ('9', '5', 'jjj');
INSERT INTO `user` VALUES ('10', '9', 'kkk');
INSERT INTO `user` VALUES ('11', '10', 'mmm');
INSERT INTO `user` VALUES ('12', '11', 'nnn');


-- ----------------------------
-- Table structure for sale
-- ----------------------------
DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`userid` int(10) DEFAULT NULL,
`moneys` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sale
-- ----------------------------
INSERT INTO `sale` VALUES ('1', '1', '10');
INSERT INTO `sale` VALUES ('2', '2', '20');
INSERT INTO `sale` VALUES ('3', '3', '30');
INSERT INTO `sale` VALUES ('4', '4', '10');
INSERT INTO `sale` VALUES ('5', '5', '20');
INSERT INTO `sale` VALUES ('6', '6', '20');
INSERT INTO `sale` VALUES ('7', '7', '10');
INSERT INTO `sale` VALUES ('8', '8', '10');
INSERT INTO `sale` VALUES ('9', '9', '30');
INSERT INTO `sale` VALUES ('10', '10', '50');
INSERT INTO `sale` VALUES ('11', '12', '10');
INSERT INTO `sale` VALUES ('12', '11', '20');
INSERT INTO `sale` VALUES ('13', '2', '10');
INSERT INTO `sale` VALUES ('14', '5', '20');
INSERT INTO `sale` VALUES ('15', '6', '10');
INSERT INTO `sale` VALUES ('16', '7', '20');
INSERT INTO `sale` VALUES ('17', '11', '10');
INSERT INTO `sale` VALUES ('18', '12', '20');
INSERT INTO `sale` VALUES ('19', '6', '20');
INSERT INTO `sale` VALUES ('20', '8', '50');




问题:
如何在TP5框架下找出每个人和下级的所有销售量总和?

代码部分<?php

namespace app\index\controller;

use think\Controller;

class Index extends Controller {
    public function index() {
        $user = db('user')->select();
        $sales = db('sale')->select();

        $res = array();
        foreach ($user as $vau) {
            $vau['mysum'] = $this->getmysum($sales, $vau['id']);
//            $vau['allsum'] = $this->getallsum($sales,$user, $vau['id']);
            $vau['allsum'] = "";
            array_push($res, $vau);
        }

        //


        $this->assign('user', $res);
        return view();
    }

    /**
     * 求所有下级销售总量
     * @param array $sales
     * @param array $user
     * @param $id
     * @return int
     */
    public function getallsum($sales = array(), $user = array(), $id) {
        $money = 0;
        foreach ($user as $vu) {
            if ($vu['pid'] == $id) {
                //找出当前下级个人总销售量
                $money = $money + $this->getmysum($sales, $vu['id']);
                //递归下级所有总销售量
                $money = $money + $this->getallsum($sales, $user, $vu['pid']);
            }
        }
        return $money;
    }


    /**
     * 求个人销售总量
     * @param array $sales
     * @param $id
     * @return int
     */
    public function getmysum($sales = array(), $id) {
        $money = 0;
        foreach ($sales as $vs) {
            if ($vs['userid'] == $id) {
                $money = $money + $vs['moneys'];
            }
        }
        return $money;
    }

}


现在问题是,写在tp5函数部分的代码,一使用就报错,
致命错误: Maximum function nesting level of '256' reached, aborting!

求问大家,有什么好办法达到我查询统计的目的?

附件 saletest.zip ( 3.1 MB 下载:2 次 )

最佳答案
评论( 相关
后面还有条评论,点击查看>>