sql-server - Conditions in T-SQL Query

Question

A table named Fruit has columns ID, Fruit name and Cost and New Cost:

  |ID | Fruit Name | Cost | New Cost
  +---+------------+------+---------
  | 1 | Apple      |  10  |    9 
  | 2 | Banana     |  20  |   22
  | 3 | Orange     |  33  |   33

I wish to query this table under one condition. If New Cost is more than Cost then Final Cost should be 0.9*Cost else Final Cost should be New Cost.

Output should look like:

  | ID | Fruit Name | Final Cost 
  +----+------------+-----------
  | 1  | Apple      |    9  
  | 2  | Banana     |   18    
  | 3  | Orange     |   29.7 

Unfortunately, I am not able to apply If condition in SQL query. Is there any way to achieve this??

标签： sql-server

Answer 1

Please check this answer

DECLARE @table TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEy,
    FruteName NVARCHAR(200),
    Cost DECIMAL(10,2),
    NewCost DECIMAL(10,2)
)

INSERT INTO @table VALUES('Apple','10',9),('Banana','20',22),('Orange','33',33)

SELECT *, IIF(NewCost>=Cost,(Cost*0.9),NewCost) AS FinalCost
FROM @table