id,summary,reporter,owner,description,type,status,priority,milestone,component,version,resolution,keywords,cc,modified_flg 408,【管理画面>売上集計】SQL文修正,kotani,somebody,"商品別集計のSQL文において、WHERE句のINをEXISTSに変えることで高速化。 [[BR]] [[BR]] 1系:/html/admin/total/index.php [[BR]] 2系:/data/class/pages/admin/total/index.php [[BR]] {{{ /** 商品別集計 **/ function lfGetOrderProducts($type, $sdate, $edate, &$objPage, $graph = true, $mode = """") { list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type); $where .= "" and del_flg=0 and status <> "" . ORDER_CANCEL; $sql = ""SELECT T1.product_id, T1.product_code, T1.product_name, T1.products_count, T1.order_count, T1.price, T1.total ""; $sql.= ""FROM ( ""; $sql.= ""SELECT product_id, product_name, product_code, price, ""; $sql.= ""COUNT(*) AS order_count, ""; $sql.= ""SUM(quantity) AS products_count, ""; $sql.= ""(price * sum(quantity)) AS total ""; ⇒ $sql.= ""FROM dtb_order_detail WHERE order_id IN (SELECT order_id FROM dtb_order WHERE $where ) ""; $sql.= ""GROUP BY product_id, product_name, product_code, price ""; $sql.= "") AS T1 ""; $sql.= ""ORDER BY T1.total DESC "";  ↓↓↓ $sql = ""SELECT T1.product_id, T1.product_code, T1.product_name as name, T1.products_count, T1.order_count, T1.price, T1.total ""; $sql.= ""FROM ( ""; $sql.= ""SELECT product_id, product_name, product_code, price, ""; $sql.= ""COUNT(*) AS order_count, ""; $sql.= ""SUM(quantity) AS products_count, ""; $sql.= ""(price * sum(quantity)) AS total ""; ⇒ $sql.= ""FROM dtb_order_detail AS T2 WHERE EXISTS (SELECT 1 FROM dtb_order AS T3 WHERE $where ) ""; $sql.= ""GROUP BY product_id, product_name, product_code, price ""; $sql.= "") AS T1 ""; $sql.= ""ORDER BY T1.total DESC ""; }}}",改善提案,closed,中,EC-CUBE2.4.0,管理画面,,修正済,,,