mysql条件输出列,基于MySQL中其他列的查询的条件列

I'm pretty sure I've seen this somewhere, but I can't find the right terminology so I'm having trouble...

Let's say I have a table with user info (let's also assume that it was created by someone who gets paid more than me, so modifying the schema is not an option.) Among the various columns of user info are columns for DOB and job title. I want a query that, based on what is in those columns, will include an extra column called "Real_Title", for example:

User_id Job_Title DOB

joe_1 manager 01/01/1950

jim_1 associate 01/01/1970

jill_1 associate 01/01/1985

jane_1 manager 01/01/1975

query:

SELECT User_id, Real_Title FROM users

IF (YEAR(DOB) < 1980 AND Job_Title = "manager")

{Real_Title = "Old Fart"}

ELSE IF (YEAR(DOB) < 1980 AND Job_Title = "associate")

{Real_Title = "Old Timer"}

ELSE IF (YEAR(DOB) > 1980 AND Job_Title = "manager")

{Real_Title = "Eager Beaver"}

ELSE IF (YEAR(DOB) > 1980 AND Job_Title = "associate")

{Real_Title = "Slacker"}

I know the above is not only wrong but also coded really inefficient, but I wanted to get the idea across.

Is there a way, without using joins, to populate a column based on information in one or more other columns in the same table?

Currently I'm using something in the PHP script after the results are obtained to channel those results into the groups I want, but if it can be done in the query, that would make porting the query to other scripts and languages much easier.

Thanks!

解决方案select User_id

,case

when (YEAR(DOB) < 1980 AND Job_Title = "manager") then 'Old Fart'

when (YEAR(DOB) < 1980 AND Job_Title = "associate") then 'Old Timer'

when (YEAR(DOB) > 1980 AND Job_Title = "manager") then 'Eager Beaver'

when (YEAR(DOB) > 1980 AND Job_Title = "associate") then 'Slacker'

else 'nobody'

end

as Real_Title

from users