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	管理画面		修正済