在mysql中根据不同的条件选择两列 - select two columns base on different condition in mysql

select two columns base on different condition in mysql

本文关键字：选择两列条件 mysql | 更新日期: 2023-09-27

<?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;
?>

是否可以将两个查询合并为一个?

谢谢。

假设您希望输出两个值，您可以使用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

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

条件和呢?

SELECT 
    SUM(CASE WHEN inv_type = 'in' THEN 1 ELSE 0 END) InSum
    SUM(CASE WHEN inv_type = 'out' THEN 1 ELSE 0 END) OutSum
WHERE
    id = 12

或者如果你只想要结果

SELECT 
    SUM(CASE inv_type WHEN 'in' THEN 1 WHEN 'out' THEN -1 ELSE 0 END) TotalSum
WHERE
    id = 12

SELECT inv_type, COALESCE(SUM(Qty), 0) 
FROM inventory
WHERE id=12 AND inv_type IN ('in', 'out')
GROUP BY inv_type