当前位置 : 首页 » 互动问答 » 正文

'IF' in 'SELECT' statement - choose output value based on column values

分类 : 互动问答 | 发布时间 : 2011-05-10 21:53:31 | 评论 : 8 | 浏览 : 1234749 | 喜欢 : 615
SELECT id, amount FROM report

I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

回答(8)

  • 1楼
  • SELECT id, 
           IF(type = 'P', amount, amount * -1) as amount
    FROM report
    

    See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html.

    Additionally, you could handle when the condition is null. In the case of a null amount:

    SELECT id, 
           IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
    FROM report
    

    The part IFNULL(amount,0) means when amount is not null return amount else return 0.

  • 2楼
  • Use a case statement:

    select id,
        case report.type
            when 'P' then amount
            when 'N' then -amount
        end as amount
    from
        `report`
    
  • 3楼
  • SELECT CompanyName, 
        CASE WHEN Country IN ('USA', 'Canada') THEN 'North America'
             WHEN Country = 'Brazil' THEN 'South America'
             ELSE 'Europe' END AS Continent
    FROM Suppliers
    ORDER BY CompanyName;
    
  • 4楼
  • select 
      id,
      case 
        when report_type = 'P' 
        then amount 
        when report_type = 'N' 
        then -amount 
        else null 
      end
    from table
    
  • 5楼
  • Most simplest way is to use a IF(). Yes Mysql allows you to do conditional logic. IF function takes 3 params CONDITION, TRUE OUTCOME, FALSE OUTCOME.

    So Logic is

    if report.type = 'p' 
        amount = amount 
    else 
        amount = -1*amount 
    

    SQL

    SELECT 
        id, IF(report.type = 'P', abs(amount), -1*abs(amount)) as amount
    FROM  report
    

    You may skip abs() if all no's are +ve only

  • 6楼
  • SELECT id, amount
    FROM report
    WHERE type='P'
    
    UNION
    
    SELECT id, (amount * -1) AS amount
    FROM report
    WHERE type = 'N'
    
    ORDER BY id;
    
  • 7楼
  • Let's try this one:

     SELECT
        id , IF(report.type = 'p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
     FROM report
    
  • 8楼
  • You can try this also

     Select id , IF(type=='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount from table
    

相关阅读:

MySQL's now() +1 day

MySQL's now() +1 day

Strange Characters in database text: Ã, Ã, ¢, â‚ €,

How can I tell what edition of SQL Server runs on the machine?

How do I limit the number of rows returned by an Oracle query after ordering?

Oracle SQL: Update a table with data from another table

MySQL date format DD/MM/YYYY select query?

How to insert a value that contains an apostrophe (single quote)?

Count work days between two dates

Multiple select statements in Single query