MySQL别名计算

Question

It confuses me.. total left calculation seems doesn't work.

I am trying to get the total voucher and get total used and the total left.

please help.

SELECT 
 (IFNULL(SUM(value), 0)) AS total_voucher,
 (
  SELECT 
    IFNULL(SUM(value), 0))
  FROM 
    voucher_history 
  WHERE 
    idUser = 1 AND isUsed = 1 AND DATE(FROM_UNIXTIME(datetime)) = '2014-03-04'
 )  AS total_used,

 (total_voucher-total_used) AS total_left

FROM 
 voucher_history 
WHERE 
 idUser = 1 AND isUsed = 0 AND DATE(FROM_UNIXTIME(datetime)) <= '2014-03-05'

Answer 1

You can do this with conditional aggregation, rather than using a subquery:

SELECT coalesce(SUM(value), 0)) AS total_voucher,
       sum(case when is_used = 1 then value else 0 end) as total_used,
       sum(case when is_used = 1 then 0 else value end) as total_left
FROM voucher_history 
WHERE idUser = 1 AND DATE(FROM_UNIXTIME(datetime)) <= '2014-03-05';

Your query has the problem that it is trying to use column aliases (total_voucher and total_used) in the same select statement. SQL does not support this. You would need to use a subquery to get that functionality.