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

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

分类 : 互动问答 | 发布时间 : 2011-05-10 21:53:31 | 评论 : 8 | 浏览 : 1234748 | 喜欢 : 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
    

相关阅读:

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

How to access remote server with local phpMyAdmin client?

Equivalent of LIMIT and OFFSET for SQL Server?