'IF 'w instrukcji' SELECT ' - wybierz wartość wyjściową na podstawie wartości kolumn

SELECT id, amount FROM report

I need amount to be amount if report.type='P' and -amount if report.type='N'. Jak dodać to do powyższego zapytania?

Author: Michael, 2011-05-10
Source

7 answers

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

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

Dodatkowo możesz poradzić sobie, gdy warunek jest null. W przypadku kwoty zerowej: 

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

Część IFNULL(amount,0) oznacza gdy kwota nie jest null return amount else return 0 .

 1050
Author: Felipe Buccioni,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-25 01:03:34

Use a case statement: 

select id,
    case report.type
        when 'P' then amount
        when 'N' then -amount
    end as amount
from
    `report`
 261
Author: mellamokb,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-10 13:55:26 
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;
 101
Author: user1210826,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-08 06:45:46 
select 
  id,
  case 
    when report_type = 'P' 
    then amount 
    when report_type = 'N' 
    then -amount 
    else null 
  end
from table
 45
Author: sang kaul,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-04 09:55:22

Najprostszym sposobem jest użycie IF () . Tak Mysql pozwala na logikę warunkową. Jeżeli funkcja przyjmuje 3 params warunek, TRUE wynik, FALSE wynik.

Więc logika jest 

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

Możesz pominąć abs (), jeśli wszystkie no są tylko + ve

 15
Author: aWebDeveloper,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-25 02:57:32 
SELECT id, amount
FROM report
WHERE type='P'

UNION

SELECT id, (amount * -1) AS amount
FROM report
WHERE type = 'N'

ORDER BY id;
 12
Author: linitux,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-05-11 14:19:01

Możesz spróbować również 

 SELECT id , IF(type='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount FROM table
 4
Author: Basant Rules,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-10-02 16:42:34