我有这个查询,它适用于MySQL 8.0,但不适用于以下版本,如MySQL 5.5。如何使其兼容,因为我在web服务器上收到了这个错误:#1064-您的SQL语法有错误;查看与MySQL服务器版本对应的手册,了解在第11行的"(partitionby-pid orderby-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
select version();
version(( |
---|
5.5.62 |
一种方法是替换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<gt;不停摆弄