MySQL相当于MS SQL的交叉应用 [英] MySQL equivalent to MS SQL's Cross Apply

问题描述

如果您习惯使用MS SQL的Cross Apply，那么您可能想知道如何在MySQL中完成相同的工作.我发现了反向问题，认为直接的问题可能会对将搜索此功能从MS SQL迁移到MySQL的人有所帮助.

If you're used to MS SQL's Cross Apply, then you may wonder how to accomplish the same thing in MySQL. I found the reverse question and thought the direct question may help anyone who'll ever search how to migrate this functionality from MS SQL to MySQL.

在此示例中，交叉应用"使您可以修改字段并在完全相同的查询中使用结果.问题是如何在MySQL中做到这一点.

In this example, Cross Apply lets you modify a field and use the result within the very same query. The question is how to do it in MySQL.

SELECT v.Var1, POWER(v.Var1, 2) AS Var2Squared
    FROM [Table] t
    CROSS APPLY (SELECT t.Column1 + t.Column2 AS Var1) v

推荐答案

您对自己的问题的回答表明，的作用非常非常有限.对于此特定问题，可以使用子查询:

Your answer to your own question suggests a very limited subset of what CROSS APPLY does. For this particular problem, you can use a subquery:

select t.*, power(var1, 2)
from (select (column1 + column2) as var1
      from aTable
     ) t;

这更昂贵，因为它导致实现子查询的开销.另一种方法是重复该表达式:

This is more expensive, because it incurs the expense of materializing the subquery. The alternative is to repeat the expression:

select (column1 + column2) as var1, power((column1 + column2), 2)
from aTable;

这些是我可以轻易想到的在MySQL中做您想要的事的唯一安全方法.

These are the only safe ways that I can readily think of in MySQL to do what you want.

这篇关于MySQL相当于MS SQL的交叉应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！