php - mysql 将三个查询合并为一个

Question

我正在从一个表中分别计算购买、返回、销售，为此我有如下查询。

$stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='purchase') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
$products_in = $stmt->fetchAll();
foreach($products_in as $product_in){
                        $purchase = $product_in['quantity_in'];
                    }

                    $stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='return purchase') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
                    $products_in = $stmt->fetchAll();
                    foreach($products_in as $product_in){
                        $return_purchase = $product_in['quantity_in'];
                    }

                    $stmt = $pdo->prepare("SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='initial_stock') AND sale_date BETWEEN '$from_date' and '$to_date'"); $stmt->execute();
                    $products_in = $stmt->fetchAll();
                    foreach($products_in as $product_in){
                        $initial_stock = $product_in['quantity_in'];
                    }

上面的查询时间很长，如果有2000个产品，计算起来需要近5分钟，有没有办法将以上三个查询合并为一个，这样可以运行得更快。也许是这样的，

(SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='initial_stock'))
as opening, 
(SELECT sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND (type='purchase')) as purchase
WHERE
sale_date BETWEEN '$from_date' and '$to_date'

注意:我不擅长 MySQL，到目前为止我只通过 PHP 运行简单的查询。

Answer 1

为什么不使用GROUP BY子句？

SELECT type, SUM(quantity) AS quantity_in, SUM(total) AS total_in
FROM silk
WHERE full_name = '$full_name'
AND type IN ('purchase', 'return purchase', 'initial_stock')
AND sale_date BETWEEN '$from_date' and '$to_date'
GROUP BY type