add a temporary column in SQL, where the values de

问题:

I have this table:

ID | name | result | 
--------------------    
 1 |  A   |   1    |
--------------------
 2 |  B   |   2    |
--------------------
 3 |  C   |   1    |
--------------------
 1 |  A   |   2    |
--------------------
 4 |  E   |   2    |
--------------------

I want to add a new temporary column next to |result|, and where result=1 the value should be 100, and where result=2 the value should be 80 so it should look like this:

ID | name | result | NewColumn|
-------------------------------    
 1 |  A   |   1    |  100     |
-------------------------------
 2 |  B   |   2    |   80     |
-------------------------------
 3 |  C   |   1    |  100     |
-------------------------------
 1 |  A   |   2    |  80      |
-------------------------------
 4 |  E   |   2    |  80      |
-------------------------------

How can I query this in SQL ?

回答1:

Use a CASE expression in your SELECT's column list - something like this:

SELECT
    ID,
    name,
    result,
    CASE
        WHEN result = 1 THEN 100
        WHEN result = 2 THEN 80
        ELSE NULL
    END AS NewColumn
FROM YourTable

Add additional WHEN expressions or alter the ELSE expression as needed.

回答2:

You could add a case statement to your query:

SELECT id, name, result, CASE result WHEN 1 THEN 100 WHEN 2 THEN 80 ELSE NULL END
from   my_table

回答3:

SELECT ID
,name
,result
,NewColumn = CASE WHEN result = 1 THEN 100 WHEN result = 2 THEN 80 END 
FROM Table1

回答4:

As the answers given above are also correct. But I will say that if you have a predefined relation or logic between the existing column and the new column then you can also achieve that without the CASE. Writing CASE for all the possible values would not be possible nor efficient.

For your existing data. I can use it something like this

SELECT ID, name, result, (20*(6-result)) as NewColumn
FROM YourTable

Here I am assuming that 1=>100, 2=>80, 3=>60, 4=>40, 5=>20.

of course this only for understanding purpose. You can create your own expression depending on the actual data.

回答5:

You would use IF Statement in Mysql

SELECT col1,col2,col3, IF(col3=1,'100', IF(col3=2,80,'')) FROM your_table