MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl
AS
    SELECT 'david' AS name,10 as age UNION SELECT 'tom',20
select
    name,
    age,
     year(current_timestamp-age) as birthyear
from
    tbl
where
    birthyear > 2010
Unknown column 'birthyear' in 'where clause'

-- standard approaches:

select *
from ( select
          name,
          age,
          year(current_timestamp)-age as birthyear
      from  tbl) s
where birthyear > 2010;

select
    name,
    age,
    year(current_timestamp())-age as birthyear
from  tbl
where year(current_timestamp())-age > 2010;


name
age
birthyear


david
10
2011




name
age
birthyear


david
10
2011


-- lateral
select
        name,
        age,
        s.birthyear
    from  tbl
    ,LATERAL(SELECT year(current_timestamp())-age as birthyear) s
    where s.birthyear > 2010;


name
age
birthyear


david
10
2011


select
            name,
            age,
            s.birthyear,
            s2.century
from  tbl
,LATERAL (SELECT year(current_timestamp())-age as birthyear) s
,LATERAL (SELECT CASE WHEN s.birthyear > 2000 THEN 'XXI' ELSE 'XX' END AS century) s2
where s.birthyear > 2010;


name
age
birthyear
century


david
10
2011
XXI