Ticket #408 (closed 改善提案: 修正済)

Opened 12 years ago

Last modified 11 years ago

【管理画面>売上集計】SQL文修正

Reported by: kotani Owned by: somebody
Priority: Milestone: EC-CUBE2.4.0
Component: 管理画面 Version:
Keywords: Cc:
修正済み:

Description (last modified by kotani) (diff)

商品別集計のSQL文において、WHERE句のINをEXISTSに変えることで高速化。

1系:/html/admin/total/index.php
2系:/data/class/pages/admin/total/index.php

    /** 商品別集計 **/
    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 ";

Change History

comment:1 Changed 12 years ago by fukuda

  • Type changed from バグ指摘 to 改善提案

comment:2 Changed 12 years ago by kotani

  • Description modified (diff)

comment:3 Changed 12 years ago by kishida

SELECT 1 FROM dtb_order AS T3 WHERE $where ) 
↓ 
SELECT 1 FROM dtb_order AS T3 WHERE T2.order_id = T3.order_id AND $where ) 
をにして改修

comment:4 Changed 12 years ago by kajiwara

  • Status changed from new to closed
  • Resolution set to 修正済
  • Milestone changed from EC-CUBE2.x.x to EC-CUBE2.4.0

r17776 にて EC-CUBE 2.4.0RC-1 に反映済

comment:5 Changed 11 years ago by Seasoft

コミュニティ r18040

Note: See TracTickets for help on using tickets.