在一次项目中需要跨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！
