MySQL 将三个查询合并为一个 - mysql join three queries into one

mysql join three queries into one

本文关键字：一个合并查询 MySQL 三个 | 更新日期: 2023-09-27

我正在从单个表中分别计算购买，退货，销售额，为此我有如下查询。

$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 个产品需要 neraly 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运行简单的查询。

为什么不使用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

我会使用一个 group by 语句，您可以在一个查询中查询所有内容。

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

在这种情况下，它会在你的PHP代码中给你这个

$stmt = $pdo->prepare("SELECT type, sum(quantity) as quantity_in, sum(total) as total_in FROM silk WHERE full_name = '$full_name' AND sale_date BETWEEN '$from_date' and '$to_date' group by type"); $stmt->execute();
$products_in = $stmt->fetchAll();
foreach($products_in as $product_in){
    switch ($product_in['type']) {
        case 'purchase':
            $purchase = $product_in['quantity_in'];
        break;
        case 'return purchase':
            $return_purchase = $product_in['quantity_in']
        break;
        case 'initial_stock':
            $initial_stock = $product_in['quantity_in'];
        break;
        // and so on...
    }
}

尝试

$stmt = $pdo->prepare("SELECT type, 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' GROUP BY type"); 
$stmt->execute();
$products_in = $stmt->fetchAll();