mysql - 通过一个 mysql-query 获取值

Question

通过以下查询，我获取不同类的值

$query = xtc_db_query("-- " . __LINE__ . __FILE__ . "
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class = 'ot_shipping'
");
$orders_total_shipping = xtc_db_fetch_array($query);
$query = xtc_db_query("-- " . __LINE__ . __FILE__ . "
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class='ot_tax'
");
$orders_total_tax = xtc_db_fetch_array($query);
$query = xtc_db_query("-- " . __LINE__ . __FILE__ . "
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class='ot_total'
");
$orders_total = xtc_db_fetch_array($query);

是否可以通过一次查询获取值？

Answer 1

可能是这样的

$query = xtc_db_query("-- " . __LINE__ . __FILE__ . "
  SELECT Case When class = 'ot_shipping' The value Else Null End As Shipping,
         Case When class = 'ot_tax' The value Else Null End As Tax,
         Case When class = 'ot_total' The value Else Null End As Total
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
");
$orders_total_shipping = xtc_db_fetch_array($query);

或者:

$query = xtc_db_query("-- " . __LINE__ . __FILE__ . "
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class = 'ot_shipping'
  Union All
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class='ot_tax'
  UNION ALL
  SELECT value
  FROM   " . TABLE_ORDERS_TOTAL . "
  WHERE  orders_id = " . $last_order . "
  AND    class='ot_total'
  ");
$orders_total = xtc_db_fetch_array($query);