MYSQL乘法如果？

Question

我有一个如下所示的查询：

//execute the SQL query and return records
$result = mysql_query("SELECT SUM(ps_order_detail.product_weight) as total_provision, COUNT(ps_order_detail.id_order_detail) as antal_ordrar, ps_customer.firstname
                   FROM ps_order_detail 
                  JOIN ps_orders ON ps_order_detail.id_order = ps_orders.id_order
                  JOIN ps_order_history ON ps_orders.id_order = ps_order_history.id_order
                  JOIN ps_customer ON ps_orders.id_customer = ps_customer.id_customer
                  WHERE MONTH(ps_order_history.date_add) = MONTH(CURDATE()) AND (ps_order_history.id_order_state) = '4' OR (ps_order_history.id_order_state) = '13'
                  GROUP BY ps_orders.id_customer
                  ORDER BY SUM(ps_order_detail.product_weight) DESC
                  ");


//fetch tha data from the database
while ($row = mysql_fetch_array($result))
{
   echo '<font size="3">';
   echo " Namn: ".$row{'firstname'}. "";
   echo " Provision: ".$row{'total_provision'}. "";
   echo " Abonnemang: ".$row{'antal_ordrar'}. "";
   echo '<br>';
   echo '</font size>';
}

它工作正常，但我想要做的是，如果结果“antal_ordrar”大于50，我想将结果“total_provision”乘以1,2（在结果中添加额外的20％ ）

我是 mysql 新手，有点卡住了。有什么想法吗？

Answer 1

SELECT SUM(ps_order_detail.product_weight) *
   IF( COUNT(ps_order_detail.id_order_detail) > 50, 1.2, 1 ) AS total_provision,
   COUNT(ps_order_detail.id_order_detail) AS antal_ordrar, ...

Answer 2

首先，将其包装在子查询中：

SELECT 
   -- use the if statement
   IF( antal_ordrar > 50, total_provision * 1.2, total_provision ),
   antal_ordrar,
   firstname
FROM
   -- create a subquery so you can reuse the data.
   ( SELECT SUM(ps_order_detail.product_weight) as total_provision, 
            COUNT(ps_order_detail.id_order_detail) as antal_ordrar, 
            ps_customer.firstname as firstname
      FROM ps_order_detail 
         -- are you sure you need this many joins?     
         JOIN ps_orders ON ps_order_detail.id_order = ps_orders.id_order
         JOIN ps_order_history ON ps_orders.id_order = ps_order_history.id_order
         JOIN ps_customer ON ps_orders.id_customer = ps_customer.id_customer
         -- group by normally uses the 'having' keyword.
         WHERE MONTH(ps_order_history.date_add) = MONTH(CURDATE()) 
            AND (ps_order_history.id_order_state) = '4' 
            OR (ps_order_history.id_order_state) = '13'
         GROUP BY ps_orders.id_customer
         ORDER BY SUM(ps_order_detail.product_weight) DESC ) 
    -- an alias for your subquery so you can treat it like a table.
    tbl;

刚刚注意到这一点。当您表示括号时，您使用的是大括号：$row{'total_provision'} 应该是 $row['total_provision']。

Answer 3

尝试添加到选择

if (antal_ordrar > 50, total_provision * 1.2, total_provision) as total_provision