iT邦幫忙

0

如何改善 while內查詢?只要查詢一次。

$product_data = $mysqli->query(
        "SELECT
          p.url as url,
          p.description as description,
          p.info as info,
          p.note as note,
          p.content as content,
          p.name as pname,
          p.icon as icon,
          p.price as price,
          p.original_price as original_price,
          p.prod_id as prod_id,
          p.stock as stock,
          p.views as views,
          s.name as sname,
          c.name as cname,
          p.add_time as add_time
        FROM `product` as p
        JOIN
          `store` as s ON p.store_id = s.store_id
        JOIN
          `category` as c ON c.category_id = p.category_id
        WHERE
          p.active = 1
        ORDER BY `prod_id` DESC
while($row = mysqli_fetch_array($product_data)){
$theme = $mysqli->query(
        "SELECT
        t.name as tname
        FROM `product` as p
        JOIN
          `product_theme` as pt ON p.prod_id = pt.prod_id
        JOIN
          `theme` as t ON t.theme_id = pt.theme_id
        WHERE
          p.prod_id = '{$row['prod_id']}' "
      );

      $buyTotal = mysqli_fetch_array($mysqli->query(
        "SELECT sum(quan) as quan FROM `stock`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));

      $viewsIp = mysqli_num_rows($mysqli->query(
        "SELECT * FROM `product_view`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));

      $pre = mysqli_num_rows($mysqli->query(
        "SELECT sum(quan) as quan FROM `user_pre`
        WHERE
          `prod_id` = '{$row['prod_id']}' "
      ));
}

直接不刪減,給大大們看看我的困擾
while 裡面的四個查詢都需要$product_data 的 $row['prod_id']
然後這這四個查詢都會有其他不同的作用
例如顯示符合條件的有幾筆、印出某個欄位的加總

<?=$pre;?>
<?=$buyTotal['quan'];?>

太直覺式的寫法了
希望有大大可以教我,感激不盡
我再套用在其他查詢上

1
犬千賀
iT邦新手 5 級 ‧ 2018-08-23 11:23:57
最佳解答

只做一次查詢的話...大概是這樣 (請自行測試喔

SELECT	p.*,
		s.*,
		c.*,
		pt.name,
		stock.quan,
		product_view.*,
		stock.quan
		
FROM `product` as p
LEFT JOIN `store` as s ON s.store_id = p.store_id
LEFT JOIN `category` as c ON c.category_id = p.category_id
LEFT JOIN( SELECT prod_id, theme.name
			FROM( product_theme, theme )
			WHERE product_theme.theme_id = theme.theme_id
	)pt ON pt.prod_id = p.prod_id
LEFT JOIN( SELECT prod_id, sum(quan) as quan FROM `stock` GROUP BY prod_id
	)stock ON stock.prod_id = p.prod_id
LEFT JOIN `product_view` ON product_view.prod_id = p.prod_id
LEFT JOIN( SELECT prod_id, sum(quan) as quan FROM `user_pre` GROUP BY prod_id
	)user_pre ON user_pre.prod_id = p.prod_id

WHERE p.active = 1

話說一定要一次查詢嗎?
你那種或是這種內部查詢跑大表 個人覺得都滿久的...

初來乍到,請各高手多多指教>"<

小松菜奈 iT邦研究生 5 級 ‧ 2018-08-23 11:25:00 檢舉

想問說怎麼避免「久」?如果真的要查大表的話

犬千賀 iT邦新手 5 級 ‧ 2018-08-23 14:39:20 檢舉

我是先看表的狀況跟條件
將某些表獨立拉資料存成array備用,能用code處裡就不要用sql

小松菜奈 iT邦研究生 5 級 ‧ 2018-08-25 11:46:11 檢舉

這真的是一套學問

1

有sum就沒辦法只查詢一次吧,有sum的部份只能另外用group by處理,
然後判斷prod_id取sum的值
沒有sum的查詢,就一樣用join處理,這樣可以只查詢三次

1
q00153
iT邦新手 3 級 ‧ 2018-08-13 15:56:04

看看下面這樣行不行 (沒測試過)

$prod_id = array();
while($row = mysqli_fetch_array($product_data)){
	$prod_id[] = $row['prod_id'];
}
$prod_id = "'" . implode("','", $prod_id) . "'";

$theme = $mysqli->query(
	"SELECT
    	`t`.`name`       as `tname`
    	SUM(`s`.`quan`)  as `quan_s`
    	SUM(`up`.`quan`) as `quan_up`
    FROM `product_theme` as `pt`
    LEFT JOIN `product`      as `p`  ON `pt`.`prod_id`  = `p`.`prod_id`
    LEFT JOIN `theme`        as `t`  ON `pt`.`theme_id` = `t`.`theme_id`
  	LEFT JOIN `stock`        as `s`  ON `pt`.`prod_id`  = `s`.`prod_id`
  	LEFT JOIN `product_view` as `pv` ON `pt`.`prod_id`  = `pv`.`prod_id`
  	LEFT JOIN `user_pre`     as `up` ON `pt`.`prod_id`  = `up`.`prod_id`
    WHERE
      	`pt`.`prod_id` IN ({$prod_id})
    GROUP BY
    	`pt`.`prod_id`"
);

我要發表回答

立即登入回答