Geek asks and answers

How can I write an IF statement in mysql

I have a table with a field type.

how can I check this field in the request:

I want to check it if type== 'a'

then bes= amount, bed= '-'

ELSE bed= amount, bes= '-'

bes and bed doesn't really exist in my table, but the amount (int)

this is my attempt:

SELECT billing.*,
    CASE billing.type WHEN 'A' THEN billing.amount AS bes, '-' AS bed ELSE billing.amount AS bed, '-' AS bes END
    FROM billing

... my searches were not useful

any solution ...

+5
source share
5 answers

You can create a condition for each line, MySQLsupports inline statements IF.

SELECT billing.*,
       IF(billing.type = 'A', billing.amount, '-') AS bes,
       IF(billing.type = 'A', '-', billing.amount) AS bed
FROM   billing
+5
source

To do this, you will need two case arguments:

SELECT billing.*,
  CASE billing.type WHEN 'A' THEN billing.amount ELSE '-' AS bes END,
  CASE billing.type WHEN 'A' THEN '-' ELSE billing.amount AS bed END
FROM billing
+4
source

CASE-WHEN-THEN-ELSE ...

:

SELECT billing.*,
CASE billing.type WHEN 'A' THEN billing.amount ELSE '-' END AS bes,
CASE billing.type WHEN 'A' THEN '-' ELSE billing.amount END AS bed 
FROM billing
+3
SELECT billing.*,
    CASE  WHEN billing.type =  'A' THEN billing.amount ELSE 0 END AS bes,
    CASE  WHEN billing.type <> 'A' THEN billing.amount ELSE 0 END AS bed
FROM billing
+3

Depending on your version of MySQL, you can use IF or one of two different syntaxes for CASE CASE (1), CASE (2).

SELECT *,
  (IF type = 'A' THEN amount ELSE '-' END IF) bes,
  (IF type = 'A' THEN '-' ELSE amount END IF) bed
FROM billing
+2
source

More articles:


All Articles