基于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:

假设我有一个包含用户信息的表（让我们假设它是由比我更多的人创建的，因此修改模式不是一种选择。）用户信息的各个列中有DOB和职称的列。我想要一个查询，基于这些列中的内容，将包含一个名为“Real_Title”的额外列，例如：

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.

目前我在获取结果后在PHP脚本中使用了一些东西来将这些结果引导到我想要的组中，但如果可以在查询中完成，那么将查询移植到其他脚本和语言会更容易。

Thanks!

谢谢！

2 个解决方案

#1

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

#2

If I understand correctly, I think you're looking for the CASE statement:

如果我理解正确，我认为您正在寻找CASE声明：

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"
            ...
            ELSE "Unknown Title"
        END) AS Real_Title
FROM users;