sql语句 IF函数的用法

一般的写法

a.date,a.total,b.manual,c.follow,d.robability（需要查询的字段）

 SELECT a.date,a.total,b.manual,c.follow,d.robability FROM 

(SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) total FROM t_table
WHERE account_id='2'  GROUP BY SUBSTRING(submit_time,1,7)) a 
LEFT JOIN 
  (SELECT SUBSTRING(submit_time,1,7) date,account_id,SUM(spread_profits) manual FROM t_table
  WHERE account_id='2'   AND follow_account_id IS NULL GROUP BY SUBSTRING(submit_time,1,7)) b 
      ON a.date = b.date 
LEFT JOIN 
  (SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) follow FROM t_table
WHERE account_id='2'    AND follow_account_id >0 AND order_number NOT LIKE 'g%' GROUP BY SUBSTRING(submit_time,1,7)) c 
      ON b.date = c.date 
              LEFT JOIN 
  (SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) robability FROM t_table
    WHERE account_id='2'   AND follow_account_id >0 AND order_number LIKE 'g%' GROUP BY SUBSTRING(submit_time,1,7)) d 
        ON c.date = d.date;

if 的写法

if(查询所需的条件)

select SUBSTRING(submit_time,1,7) as date ,sum(spread_profits) total ,
if(follow_account_id IS NULL,SUM(spread_profits),0) as manual,
if(follow_account_id >0 AND order_number NOT LIKE 'g%',SUM(spread_profits),0) as follow,
if(follow_account_id >0 AND order_number LIKE 'g%',SUM(spread_profits),0) as robability  from t_table
 WHERE account_id='2'  AND submit_time BETWEEN begin AND end  GROUP BY SUBSTRING(submit_time,1,7));