如何使此sql查询兼容mysql的旧版本，如V.5.5及以上版本？

Question

我有一个查询，它可以在MySQL8.0上运行，但不能在下面的版本上运行，比如MySQL5.5。当我在web服务器上遇到这个错误时，如何使其兼容：#1064-您的SQL语法中有一个错误；查看与您的MySQL服务器版本相对应的手册，以了解第11行“（partition by pid order by Age”附近使用的正确语法

SELECT p.id,  p.pid, p.name, 
       MAX(CASE WHEN t.dr = 1 THEN t.name END) as name1,
       MAX(CASE WHEN t.dr = 1 THEN age END) as age1,
       MAX(CASE WHEN t.dr = 2 THEN t.name END) as name2,
         MAX(CASE WHEN t.dr = 2 THEN t.age END) as age2,
            MAX(CASE WHEN t.dr = 3 THEN t.name END) as name3,
         MAX(CASE WHEN t.dr = 3 THEN t.age END) as age3,
             MAX(CASE WHEN t.dr = 4 THEN t.name END) as name4,
         MAX(CASE WHEN t.dr = 4 THEN t.age END) as age4
FROM Table1 p
join (select id , pid, name, age,  DENSE_RANK() OVER (partition by pid order by age) as dr
from Table2) t on p.pid= t.pid
group by p.id, p.pid, p.name

https://dbfiddle.uk/？rdbms=mysql_8.0&fiddle=D324E7654218DAE3D1168DF2BF529099

Answer 1

 select version();

Answer 2

一种方法是替换dense_rank()的相关子查询：

FROM Table1 p JOIN
     (SELECT t2.*,
             (SELECT COUNT(DISTINCT tt2.age)
              FROM table2 tt2
              WHERE tt2.pid = t2.pid AND
                    tt2.age <= t2.age
             ) as dr
      FROM Table2 t2
     ) t 
     ON p.pid = t.pid

这是一个dB<>小提琴。