我在Postgres中有一个复杂的查询,试图在MySQL中进行转换。 Postgres查询具有三个链接查询。前两个创建两个公用表,最后一个查询对这两个公用表进行联接。查询的简化版本看起来像这样。有没有办法在MySQL中联接这两个常用表?我需要运行5.6、5.7和8.0的查询,因此8.0中CTE的新功能不是解决方案。
(Select table1.y_id as year_id,
SUM(table1.total_weight) AS metric_value
from (SELECT student_name,y_id,total_weight from student_metrics where y_id>10 ) table1
group by year_id
order by metric_value DESC
limit by 5
)table2
第三个查询应在
table1.y_id = table2.year_id.
上执行table1和table2的联接要大致了解每个查询的功能,请执行以下操作:
查询1从主表(例如表1)中获取数据并存储
根据某些条件将其放在通用表中
查询2根据用户指定的列对查询1中获得的行进行分组和排序,并将其限制为“ N”
查询3通过在table1上执行联接来仅返回这N个唯一ID(从表2获得)的所有详细信息(在表1上)。
table2.id
最佳答案
您可以简单地重复table1
子查询:
select
table1.*
from
(select student_name,y_id,total_weight from student_metrics where y_id>10) as table1
inner join (
select tbl1.y_id as year_id,
sum(tbl1.total_weight) as metric_value
from
(select student_name,y_id,total_weight from student_metrics where y_id>10 ) as tbl1
group by tbl1.y_id
order by sum(tbl1.total_weight) desc
limit by 5
) as table2 on table1.y_id = table2.year_id;