我有一个查询，它可以在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
一种方法是替换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<>小提琴。
select version();
