SQL syntax case usage

I saw a friend in the group discussing SQL case and when usage. I did not use it very much. I just googled it and wrote a small example to learn it.

The following is a small example:

Select ID, username, <br/> case <br/> when usertype = '1' then' normal user '<br/> when usertype = '2' then' legal user' <br/> else 'Other type' <br/> end as user type <br/> from userbaseinfo <br/> where ID <50 <br/> order by usertype

The search content:

Case may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can use case in the where <br/> clause. <Br/> first, let's take a look at the case syntax. In a general SELECT statement, the syntax is as follows: <br/> select = <br/> case <br/> when then <br/> else <br/> end <br/> above <br/> SPAN class = 'wp _ keywordlink'> Code </span> must replace the content in angle brackets with specific parameters. The following is a simple example: <br/> Use pubs <br/> go <br/> select <br/> title, <br/> 'price range' = <br/> case <br/> when price is null then 'unpriced' <br/> when price <10 then 'bargain' <br /> when price between 10 and 20 then 'average' <br/> else 'Gift to impress relatives '<br/> end <br/> from titles <br/> order price <br/> go <br/> the above format cannot be used for Informix, can be: <br/> Use pubs <br/> go <br/> select <br/> title, <br/> case <br/> when Price is null then 'unpriced' <br/> when price <10 then 'bargain' <br/> when price between 10 and 20 then 'average' <br/> else' gift to impress relatives '<br/> end <br/> price range <br/> from titles <br/> order by price <br/> go </P> <p> this is a typical case usage, however, you can use case to do more things. For example, the case in the following group by clause: <br/> select 'number of tidles ', count (*) <br/> from titles <br/> group by <br/> case <br/> when price is null then 'unpriced' <br/> when price <10 then' bargain '<br/> when price between 10 and 20 then 'average' <br/> else 'Gift to impress relatives' <br/> end <br/> go <br/> you can even combine these options, add an order by clause as follows: <br/> Use pubs <br/> go <br/> select <br/> case <br/> when price is null N 'unpriced' <br/> when price <10 then 'bargain' <br/> when price between 10 and 20 then 'average' <br/> else 'Gift to impress relatives '<br/> end as range, <br/> title <br/> from titles <br/> group by <br/> case <br/> when price is null then 'unpriced' <br/> when price <10 then 'bargain' <br/> when price between 10 and 20 then 'average' <br/> else 'Gift to impress relatives '<br/> end, <br/> title <br/> orde R by <br/> case <br/> when price is null then 'unpriced' <br/> when price <10 then 'bargain' <br/> when price between 10 and 20 then 'average' <br/> else 'Gift to impress relatives '<br/> end, <br/> title <br/> go <br/> for Informix, example <br/> select <br/> case when td_acc1_no [610] = '000000' then get_contrast (td_acc1_no) <br/> else get_contrast (td_td_acct_no) <br/> end sx_acct_no <br/>, sum (td_actu_amt) SX _ Bal <br/> from dcc_tdacnacn where td_td_acct_no in ('20140901', <br/> '20160901') <br/> group by 1 </P> <p> note, to use case in the group by block, the query statement must repeat the case block in the select block in the group by block. <Br/> Besides selecting custom fields, case is useful in many cases. Further, you can get the grouping sorting result set that you previously thought was impossible to get.