I am new to SQl and try to learn on my own.

i am learn the usage of if and else statement in SQL

Here is the data where I am trying to use, if or else statement in the below table, i wish to have the comments updated based on the age using the sql query, let say if the age is between 22 and 25, comments" under graduate"

Age: 26 to 27, comments " post graduate"
Age: 28 to 30, comments "working and single"
Age: 31 to 33, comments " middle level manager and married"

Table name: persons

personid  lastname  firstname   age comments
1         Cardinal  Tom         22
2         prabhu    priya       33
3         bhandari  abhijeet    24
4         Harry     Bob         25
5         krishna   anand       29
6         hari      hara        31
7         ram       hara        27
8         kulkarni  manoj       35
9         joshi     santosh     28

---

How To Use Case

Try with CASE Statement

Select personid,lastname,firstname,age,
       Case when age between 26 and 27 then 'post graduate'
            when age between 28 and 30 then 'working and single' 
            when age between 31 and 33 then ' middle level manager and married' 
            Else 'Nil'
       End comments 
from persons

---

I would probably go with a CASE statement

Something like

SELECT  *,
        CASE
            WHEN age BETWEEN 22 AND 25 
                THEN 'under graduate'
            WHEN age BETWEEN 26 AND 27
                THEN 'post graduate'
            WHEN age BETWEEN 28 AND 30 
                THEN 'working and single'
            WHEN age BETWEEN 31 AND 33
                THEN 'middle level manager and married'
            ELSE 'TADA'
        END Comment
FROM    persons

---

As by your requirement, we can't use if...else statement. Case..when statement will be most suitable one. And another one thing We can't able to use if...else inside of any queries(I mean inside of select, insert, update).

And your

Select personid,lastname,firstname,age,
   Case when age between 26 and 27 then 'post graduate'
   Case when age between 28 and 30 then 'working and single' 
   Case when age between 31 and 33 then ' middle level manager and married' 
   Else 'Nil'
   End comments 
from persons

链接地址: http://www.djcxy.com/p/94360.html

上一篇: 基于if命令的默认值

下一篇: SQL，多个if语句，然后呢？