基于 MySQL 中其他列的查询条件列 [英] Conditional column for query based on other columns in MySQL

问题描述

我很确定我在某处看到过这个，但我找不到正确的术语，所以我遇到了麻烦......

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

假设我有一个包含用户信息的表(我们还假设它是由比我获得​​更高报酬的人创建的，因此修改架构不是一种选择.)在用户信息的各个列中，有 DOB 列和职称.我想要一个查询，根据这些列中的内容，将包含一个名为Real_Title"的额外列，例如:

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

查询:

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?

目前，在获得结果后，我在 PHP 脚本中使用某些东西将这些结果引导到我想要的组中，但如果可以在查询中完成，那将使查询移植到其他脚本和语言变得很多更容易.

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.

谢谢！

推荐答案

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

这篇关于基于 MySQL 中其他列的查询条件列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！