MySQL实战基础知识入门(9)：MYSQL跨4个表的高效查询代码的解决方案

在一次项目中需要跨4个表查询相关数据，形成一个json供前端调用。

失败的解决方案

    global $db, $res;
        dbc();
        $sql = "select a.uniacid,SUM(a.should_paid) AS totalPays,COUNT(a.id) AS transTotal,MAX(a.paid_at) AS paid_at,b.uniacid,b.name,c.uniacid,COUNT(c.id) AS memberTotal,c.created_at from " . $db->table('orders');
        $sql .= " AS a, " . $db->table('set_basic') . " AS b," . $db->table('member') . "AS c";// . $db->table('leader') . "AS d";
        $sql .= " WHERE a.paid_at  0 AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE() AND a.uniacid = b.uniacid";
        $sql .= " AND date_format(c.created_at,'%Y-%m-%d') = CURDATE() AND a.uniacid = c.uniacid";
        $sql .= " GROUP BY a.uniacid ORDER BY totalPays DESC LIMIT 15";
        $row = $db->queryall($sql);
        $res["data"] = $row;
        die(json_encode_lockdata($res));

**产生的问题：**页面直接奔溃，下载进度转了几圈就直接显示网页无法显示了。 **产生的原因：**四个数据表都要根据uniacid进行唯一的键值关系查找匹配，但是每个表同时还要关联很多WHERE条件，在实际开发过程中，自己都不知道如何排列哪个作为优先的筛选条件。数据表中的member、leader都超过20万条记录。

解决方案

1. 由于是筛选条件是无法确认优先顺序的，在WHERE筛选的时候。那么首先解决的就是要先确保业务流程的中优先筛选条件；
2. 根据优先筛选的条件产生的结果集，由结果集内的uniacid再对member、leader两个数据表进行二次循环，实现高效、快捷的查询；
3. 对上述的结果集，进行JSON编码，提供前端使用；

        //筛选全部记录并随前端自动分页展示;
        $sql = "select SUM(a.should_paid) AS totalPays,a.paid_at,a.uniacid,b.uniacid,b.name from " . $db->table('orders');
        $sql .= " as a, " . $db->table('set_basic') . " as b where a.uniacid = b.uniacid AND a.paid_at  '' ";
        $sql .= " GROUP BY b.name ORDER BY totalPays DESC";
        $sql .= " LIMIT " . $limit . "," . $pagesize;
        $row = $db->queryall($sql);
        
        //获取对应的站点会员数量;
        foreach ($row as $k => $v) {
            $sql_m = "select COUNT(id) AS memberTotal from " . $db->table('member');
            $sql_m .= " WHERE legitimate = 1 AND uniacid =" . $v['uniacid'];
            $sql_m .= " GROUP BY uniacid";
            $row[$k]["member"] = $db->queryall($sql_m);
        }

        //获取对应站点团长的数量;
        foreach ($row as $k => $v) {
            $sql_l = "select IFNULL(COUNT(id),0) AS leaderTotal from " . $db->table('leader');
            $sql_l .= " WHERE server = 1 AND legitimate = 1  AND uniacid =" . $v['uniacid'];
            $sql_l .= " GROUP BY uniacid";
            $row[$k]["leader"] = $db->queryall($sql_l);
        }
        $res["data"] = $row;
        die(json_encode_lockdata($res));

Done！