Case when a value is different to other value, SQL

问题:

I have this table structure for table prices:

CREATE TABLE prices
(
     id int, 
     priceFrom int, 
     priceUp int
);

INSERT INTO prices (id, priceFrom, priceUp)
VALUES (1, 23, 23), (2, 0, 0), (3, 12, 13),
       (4, 40, 40), (5, 15, 15), (6, 0, 0);

This is the result:

I have this query:

select 
    pricefrom, priceup,
    case
        when pricefrom = 0 then null
        when priceFrom <> priceUp then priceFrom + ' - ' + priceUp
        when priceFrom = priceUp then priceFrom
    end as FinalPrice
from 
    prices

what I need is to do a case when

1. pricefrom = 0 then show null
2. pricefrom = priceup then show the price
3. At least if pricefrom != priceup I want to show for example this: 12(pricefrom) - 13(priceup)

but in my query in this line:

I try to do this with <> but in the result appears the sum for both numbers:

How can I fix this?

回答1:

I think you're looking for the concat function here.

 select pricefrom, priceup,
case
when pricefrom = 0 then null
when priceFrom <> priceUp then concat(priceFrom, ' - ', priceUp)
when priceFrom = priceUp then cast(priceFrom as varchar(8))
end as FinalPrice
from prices

This link will probably be helpful

MySQL combine two columns into one column

回答2:

You didn't provide the error, but by the format of your CASE EXPRESSION I'm assuming it's throwing an error due to conversion.

You should use CAST to VARCHAR :

 select pricefrom, priceup,
       case
           when pricefrom = 0 then ''
           when priceFrom <> priceUp then CAST(priceFrom as varchar(10)) + ' - ' + CAST(priceUp as varchar(10))
           when priceFrom = priceUp then CAST(priceFrom as varchar(10))
      end as FinalPrice
 from prices

I'm not sure about the first WHEN , but you should know that :

usually the first condition of a CASE EXPRESSION will determine the type of the column, so, if the first THEN placing an integer, this column will be an integer.

since you are putting null value in it, I'm not sure which type the column will be evaluated to, so it can still throw an error, but you can give it a try :

           when pricefrom = 0 then null

Note: Like @aakashpugta.0205 answer, using CONCAT() the convertion is automatic, but you should know that CONCAT() is available on SQL-Server only since 2012, so in older versions that won't work!

Let me also reffer you to an intersting article about CASE EXPRESSION secrets .

回答3:

You have to CAST to VARCHAR:

select pricefrom, priceup,
       case
          when pricefrom = 0 then null
          when priceFrom <> priceUp then concat(cast(priceFrom as varchar(8)),
                                                ' - ', 
                                                cast(priceUp as varchar(8)))
          when priceFrom = priceUp then cast(priceFrom as varchar(8))
       end as FinalPrice
from prices

回答4:

You should cast the price fields as string so that SQL understands that you don't want to treat them as numbers and do a mathematical operation:

select pricefrom, priceup,
case
when pricefrom = 0 then null
when priceFrom <> priceUp then cast(priceFrom as varchar) + ' - ' + cast(priceUp as varchar)
when priceFrom = priceUp then priceFrom
end as FinalPrice
from prices