mysql 先执行子查询_请教php mysql高手把这个子查询改下,能否改成先运行子查询里的数据,然后再用in来指定,运行速度太慢了...

publicfunctionitems($filter=array(),$orderby=null,$p=1,$l=50,&$count=0){if($attrs=$filter['attrs']){if($attr_ids=K::M('verify/check')->ids($attrs)){if(is_numeric($attr_id...

public function items($filter=array(), $orderby=null, $p=1, $l=50, &$count=0) { if($attrs = $filter['attrs']){ if($attr_ids = K::M('verify/check')->ids($attrs)){ if(is_numeric($attr_ids)){ $attr_sql = "SELECT case_id FROM ".$this->table('case_attr')." WHERE attr_value_id=$attr_ids GROUP BY case_id"; }else{ $attr_count = array_sum(explode(',', $attr_ids)); $attr_sql = "SELECT case_id FROM ".$this->table('case_attr')." WHERE attr_value_id IN($attr_ids) GROUP BY case_id HAVING SUM(attr_value_id)=$attr_count"; } } } unset($filter['attrs']); $where = $this->where($filter); if($attr_sql){ $where .= " AND case_id IN($attr_sql)"; } $orderby = $this->order($orderby); $limit = $this->limit($p, $l); $items = array(); $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM ".$this->table($this->_table)." WHERE $where $orderby $limit"; if($rs = $this->db->query($sql)){ while($row = $rs->fetch()){ $row = $this->_format_row($row); $items[$row[$this->_pk]] = $row; } $count = $this->db->GetOne("SELECT FOUND_ROWS()"); } return $items; }

两个表的结构如下

实际运行的语句

SELECT SQL_CALC_FOUND_ROWS * FROM jh_case WHERE `audit`='1' AND `closed`='0' AND case_id in (SELECT case_id FROM jh_case_attr WHERE attr_value_id IN(76,30,42) GROUP BY case_id HAVING SUM(attr_value_id)=148) ORDER BY `orderby` DESC,`case_id` DESC LIMIT 15

展开

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值