地区表就是存储全国各地市县,一共几千条吧guest 表里的province,city,country 是要存储客户所在的具体省市县 while($array2 = mysql_fetch_array($query2)) { $number_money = $_SGLOBAL['db']->query("SELECT sum(money) as money FROM `oa_contract` WHERE fid=".$array2['id']); $array_money = $_SGLOBAL['db']->fetch_array($number_money); $nums += $array_money['money']; } 主要问题在这里的循环上,因为如果权限够大,要循环110万次以上,此句屏蔽掉,数据载入瞬间就出来了
类似这样。。因为guset表要返回所有数据,故作为left join 的左表。 ============================================================= select id,sum(money) as money from guset g left join area a on a.area_id=g.area_id left join oa_construct o on o.fid=g.id where g.id in(1,2,3,4,5,6) group by o.fid
谢谢你的那个语句我运行了一下,速度很快,数据有了,但是分组却不是我希望的方式SELECT sum(money) as money FROM `oa_contract` WHERE fid in (SELECT id FROM `oa_guest` WHERE $wheres $mywheres)我把语句改成这样了,可以了,速度也很快此贴就遮掩结贴吧,还是谢谢你,分是你的!
1 给查询条件适当加上索引. 2 优化程序.减少不必要的循环,比如说,你在算总数的时候,完全可以用一条sql语句实现,没有必要进行哪么多的循环,多次请求查询再算总值. $nums = 0; $query3 = "SELECT SUM(money) AS money FROM `oa_contract` oa LEFT JOIN guest ON oa.fid = guest.id WHERE guest.uid in (1,2,400,1000,....) and $mywheres"; $array3 = mysql_fetch_array($query2); $nums = $array3['money'];
//这个是地市的表,可通过条件再加入详细查询条件,比如说只查询某省; $query = mysql_query("SELECT * FROM area where order by area_id asc") while(mysql_fetch_array($query)) { if($values_c['parent_id'] == 0 ) $mywheres = "province=".$values_c['area_id']; else $mywheres = "city=".$values_c['area_id']; //这个是员工表,每人查询员工的数量也不一样, $query2 = mysql_query("SELECT id FROM guest where uid in (1,2,400,1000,....) and $mywheres order by area_id asc") //这里需要知道单位的数量 $number_counts = $_SGLOBAL['db']->num_rows($number);
$nums = 0; //这里要查询出每个单位下的合同总金额 while($array2 = mysql_fetch_array($query2)) { $number_money = $_SGLOBAL['db']->query("SELECT sum(money) as money FROM `oa_contract` WHERE fid=".$array2['id']); $array_money = $_SGLOBAL['db']->fetch_array($number_money); $nums += $array_money['money']; } } 楼主:你发的东西也太难看了啊!!以后用染色板加上,,,
只知道这个,可能可以吧!
你可以看看是否能够通过group by的操作来优化查询结果。
再设置好索引字段,把能够用整形表示的字段尽量都用整形存储
explain select 跟踪一下
1.guest 表直接建立个字段area_id与地区表关联就可以了,何必新开字段province,city这样呢?
2.根据1修改关联关系,这样一条联表sql就可以了,没必要分3条sql在业务端做。
地区表就是存储全国各地市县,一共几千条吧guest 表里的province,city,country 是要存储客户所在的具体省市县
while($array2 = mysql_fetch_array($query2))
{
$number_money = $_SGLOBAL['db']->query("SELECT sum(money) as money FROM `oa_contract` WHERE fid=".$array2['id']);
$array_money = $_SGLOBAL['db']->fetch_array($number_money);
$nums += $array_money['money'];
} 主要问题在这里的循环上,因为如果权限够大,要循环110万次以上,此句屏蔽掉,数据载入瞬间就出来了
=============================================================
select id,sum(money) as money from guset g
left join area a on a.area_id=g.area_id
left join oa_construct o on o.fid=g.id
where g.id in(1,2,3,4,5,6)
group by o.fid
======================================================================================
这个很没有必要,area表是树型结构对不对?你只要知道area_id是不是可以回溯找到上面一层?
为什么要province,city,country三个字段,你可以在area表加个标识,表明数据是省,还是市,还是国家
guset表通过area_id与area表关联,只要知道area_id值,就可以关联到area表知道这个area到底是省还是市还是国家。你这样设计连第一范式都不遵守阿。
谢谢你的那个语句我运行了一下,速度很快,数据有了,但是分组却不是我希望的方式SELECT sum(money) as money FROM `oa_contract` WHERE fid in (SELECT id FROM `oa_guest` WHERE $wheres $mywheres)我把语句改成这样了,可以了,速度也很快此贴就遮掩结贴吧,还是谢谢你,分是你的!
2 优化程序.减少不必要的循环,比如说,你在算总数的时候,完全可以用一条sql语句实现,没有必要进行哪么多的循环,多次请求查询再算总值.
$nums = 0;
$query3 = "SELECT SUM(money) AS money FROM `oa_contract` oa LEFT JOIN guest ON oa.fid = guest.id WHERE guest.uid in (1,2,400,1000,....) and $mywheres";
$array3 = mysql_fetch_array($query2);
$nums = $array3['money'];
//这个是地市的表,可通过条件再加入详细查询条件,比如说只查询某省;
$query = mysql_query("SELECT * FROM area where order by area_id asc")
while(mysql_fetch_array($query))
{
if($values_c['parent_id'] == 0 )
$mywheres = "province=".$values_c['area_id'];
else
$mywheres = "city=".$values_c['area_id']; //这个是员工表,每人查询员工的数量也不一样,
$query2 = mysql_query("SELECT id FROM guest where uid in (1,2,400,1000,....) and $mywheres order by area_id asc") //这里需要知道单位的数量
$number_counts = $_SGLOBAL['db']->num_rows($number);
$nums = 0;
//这里要查询出每个单位下的合同总金额
while($array2 = mysql_fetch_array($query2))
{
$number_money = $_SGLOBAL['db']->query("SELECT sum(money) as money FROM `oa_contract` WHERE fid=".$array2['id']); $array_money = $_SGLOBAL['db']->fetch_array($number_money); $nums += $array_money['money']; }
} 楼主:你发的东西也太难看了啊!!以后用染色板加上,,,