我应该如何选择一列与另一列相关的语句？
I have a table that look like this:
我有一个看起来像这样的表:
The result that I need is which empid that their passportno has been changed by user='12345'. empid is employee ID and userid is HR Personnel.
我需要的结果是,他们的passportno已被user ='12345'更改。 empid是员工ID,userid是HR Personnel。
This is my query:
这是我的查询:
SELECT * FROM audit_is_emppersonal
WHERE date between '09/25/2017' and
'12/12/2017'
and userid='12345'
**update
This is my expected result:
这是我的预期结果:
As u can see, it only appear empid that passportno has been changed by userid='12345'
正如你所看到的那样,只有用户名='12345'才能改变passportno
4 个解决方案
#1
0
From what you are showing it seems you always have two records for one date, one with action 'BEFORE UPD', one with action 'AFTER UPD' and you want to see when passwordno was changed in such a process.
根据你所显示的内容,你似乎总是有一个日期的两条记录,一条带有动作'BEFORE UPD',一条带有动作'AFTER UPD',你想知道在这样的过程中何时更改了passwordno。
select
bu.userid,
bu.date,
bu.empid,
bu.passportno as passportno_before_update,
au.passportno as passportno_after_update
from (select * from audit_is_emppersonal where action = 'BEFORE UPD') bu
join (select * from audit_is_emppersonal where action = 'AFTER UPD') au
on au.userid = bu.userid
and au.empid = bu.empid
and au.date = bu.date
and au.passportno <> bu.passportno
where bu.userid = 12345
and bu.date between '20170925' and '20171212';
(You may have to adjust the ON clause slightly according to what must match exactly to identify two records for the same update.)
(您可能必须根据必须完全匹配的内容稍微调整ON子句,以便为同一更新标识两个记录。)