MySQL不存在未知列错误

Question

for a couple of hours I have been working on this problem.

I have a table in MySQL called person_state

----------
person_state
----------------------------
id | person_id | state | date
----------------------------

I need to get active persons last activation dates, The state of every person is has been changed many times(States are : active, passive, waiting, blocked). If a person is activated then deactivated, my query should not get it.

My query is

select id as activation_id, person_id as active_person_id
  from person_state
  where state = 'active' 
  and
  not exists(
         select * from person_state 
         where person_id = active_person_id
         and
         id > activation_id
    ) 

I am getting error Unknown column 'active_person_id' in 'where clause' .

Thanks for your your time

Answer 1

WHERE gets executed before SELECT and hence, you can't use column aliases inside it, try with column name instead, e.g.:

select id as activation_id, person_id as active_person_id
  from person_state ps
  where state = 'active' 
  and
  not exists(
         select id from person_state 
         where person_id = ps.person_id
         and
         id > ps.activation_id
    )