mysql - 如果不满足条件，则替换满足条件的行中的值

Question

嗨，在我的 sql 中，我遇到以下问题来自类似于以下内容的源表:

表1:

Employee    Role    Contract Hours  IS primary Role Valid from
1   Role A  35  Y   01/03/2015
1   Role B  35  Y   01/06/2016
1   Role C  0   N   01/07/2016
2   Role A  20  Y   01/01/2016
2   Role B  0   N   01/01/2016
2   Role C  25  Y   01/04/2016
3   Role A  35  Y   01/04/2016

因此，在员工 1 的示例中，他于 2015 年 3 月 1 日开始在一家公司工作，于 2016 年 6 月 1 日更换职位，并于 2017 年 7 月 1 日添加了第二个临时角色我需要一个返回任何角色和最新主要角色及其契约(Contract)时间的查询:

Employee    Role    Contract Hours  Valid from  Primary Role – Contract Hours
1   Role A  35  01/03/2015  Role A – 35
1   Role B  35  01/06/2016  Role B – 35
1   Role C  0   01/07/2016  Role B – 35
2   Role A  20  01/01/2016  Role A – 20
2   Role B  0   01/01/2016  Role A – 20 
2   Role C  25  01/04/2016  Role C – 25 
3   Role A  35  01/04/2016  Role A – 35 

如果主要角色是 N，我得到的最佳结果会为附加列返回空值

Select 
“Employee”,
“Role”,
“Contract Hours”,
“Valid From”,
 Case
When “Is primary Role” = Y
Then “Role” + ‘-‘ + “Contract Hours”
    Else NULL
 END as “Primary Role – Contract Hours”

来自表1

但我需要找到一种方法，在任何次要角色的有效起始日期之前添加最新的主要值，而不是“else Null”。

Answer 1

/*
DROP TABLE T;
CREATE TABLE T (Employee int,   Role varchar(10),    ContractHours  int,  ISprimaryRole char(1), Validfrom date);
insert into t values
(1,   'Role A',  35 , 'Y' ,  '2015-03-01'),
(1,   'Role B',  35 , 'Y' ,  '2016-06-01'),
(1,   'Role C',  0  , 'N' ,  '2016-07-01'),
(2,   'Role A',  20 , 'Y' ,  '2016-01-01'),
(2,   'Role B',  0  , 'N' ,  '2016-01-01'),
(2,   'Role C' , 25 , 'Y' ,  '2016-04-01'),
(3,   'Role A' , 35 , 'Y' ,  '2016-04-01');
*/


select *
from
(
select *,
        (Select max(t1.validfrom) from t t1 where t1.Employee = t.employee and t1.isprimaryrole = 'Y' and t1.Validfrom <= t.validfrom) maxisp
from    t
) s
left outer join t t1 on t1.employee = s.employee and t1.Validfrom = s.maxisp and t1.isprimaryrole = 'Y'

结果

+----------+--------+---------------+---------------+------------+------------+----------+--------+---------------+---------------+------------+
| Employee | Role   | ContractHours | ISprimaryRole | Validfrom  | maxisp     | Employee | Role   | ContractHours | ISprimaryRole | Validfrom  |
+----------+--------+---------------+---------------+------------+------------+----------+--------+---------------+---------------+------------+
|        1 | Role A |            35 | Y             | 2015-03-01 | 2015-03-01 |        1 | Role A |            35 | Y             | 2015-03-01 |
|        1 | Role B |            35 | Y             | 2016-06-01 | 2016-06-01 |        1 | Role B |            35 | Y             | 2016-06-01 |
|        1 | Role C |             0 | N             | 2016-07-01 | 2016-06-01 |        1 | Role B |            35 | Y             | 2016-06-01 |
|        2 | Role A |            20 | Y             | 2016-01-01 | 2016-01-01 |        2 | Role A |            20 | Y             | 2016-01-01 |
|        2 | Role B |             0 | N             | 2016-01-01 | 2016-01-01 |        2 | Role A |            20 | Y             | 2016-01-01 |
|        2 | Role C |            25 | Y             | 2016-04-01 | 2016-04-01 |        2 | Role C |            25 | Y             | 2016-04-01 |
|        3 | Role A |            35 | Y             | 2016-04-01 | 2016-04-01 |        3 | Role A |            35 | Y             | 2016-04-01 |
+----------+--------+---------------+---------------+------------+------------+----------+--------+---------------+---------------+------------+