php - 根据mysql中的不同条件选择两列

Question

<?php
    $inventory_in = $db->get_one("SELECT COALESCE(SUM(Qty), 0) 
                    FROM inventory Where id=12 AND inv_type = 'in' LIMIT 1");

    $inventory_out = $db->get_one("SELECT COALESCE(SUM(Qty), 0) 
                     FROM inventory Where id=12 AND inv_type = 'out' LIMIT 1");

    $inventory = $inventory_in - $inventory_out;
?>

可以将两个查询合并为一个吗？

谢谢。

Answer 1

假设您想要两个值输出，您可以使用 SUM/CASE

SELECT
      COALESCE(SUM(CASE WHEN inv_type = 'in' THEN Qty ELSE 0 END ), 0) as  Sum1,
      COALESCE(SUM(CASE WHEN inv_type = 'out' THEN Qty ELSE 0 END ), 0) as  Sum2
FROM inventory 
WHERE id=12 
LIMIT 1

您可能需要将 and inv_type in ('in', 'out') 添加到 where 子句，但这不是必需的。