SQL SELECT 语句表达式值重用于其他表达式 [英] SQL SELECT statement expression value reuse for other expression

问题描述

我在 mysql 中有一个包含大量行数的表(尽管我正在寻找通用的 SQL 解决方案)

I have a table with huge row count in mysql (though I am looking for generic SQL solution)

very_big_table(INT a, INT b, INT c, ...)

我想要 SELECT 语句

I wanted SELECT statement

SELECT a, 
    (b + c) as expression1,
    (b + c + a) AS expression2 -- basically (expression1 + a)
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

只要表达式 1 很简单，这看起来不错并且易于阅读.
但是当 CASE-WHEN/IFNULL()/SUM()/MIN()/STRCAT() 或某些运算符在这些表达式中起作用时，它很难阅读和调试.

this looks good and easily readable as long as the expression1 is simple.
But when CASE-WHEN/IFNULL()/SUM()/MIN()/STRCAT() or some Operator comes into play in these expressions its difficult to read and debug.

我已经解决了一些已经问过的问题
将 mysql 值分配给内联变量
将一列的值用于另一列 (SQL Server)?
如何在同一个选择中使用条件列值声明?

I have gone through some of the already asked questions
assigning mysql value to variable inline
Use value of a column for another column (SQL Server)?
How to use conditional columns values in the same select statement?

但是如果我使用描述的方法

But if I use the approaches described something like

SELECT a, 
    expression1,
    (expression1 + a) AS expression2
FROM 
    (SELECT a,
        (b + c) AS expression1
    FROM very_big_table
    WHERE ...
    GROUP BY a) as inner_table
ORDER BY a DESC

这工作正常，但此查询的执行时间要多出 70 倍.至少当我发射它时，虽然只有一次.
如果我在输出列中有多个级别的表达式怎么办?

this works fine, but this query is taking some 70x more time to execute. Atleast when i fired it, though only once.
what if I have multiple levels of the expressions in the output columns?

有没有什么优雅的方法可以在不影响可读性的情况下处理这个问题?

Is there any elegant way to deal with this, without compromising readability?

顺便说一句，为什么 SQL 标准或供应商不支持 select 语句中的这个表达式重用或别名引用?(假设在单个 SELECT 语句表达式中没有循环评估.在这种情况下编译器失败)

BTW why isnt this expression reuse or alias reference in select statement not supported by SQL standards or vendors? (supposing there are no cyclic evaluation in the single SELECT statement expressions. in that case the compiler fails)

推荐答案

您可以使用用户定义的变量来解决您的问题.您的 SQL 可以重写为:

You can use User-Defined Variable to solve your problem. Your SQL can be rewritten as:

SELECT a, 
    @expr1 := (b + c) as expression1,
    (@expr1 + a) AS expression2
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

您可以参考这篇文章.

这篇关于SQL SELECT 语句表达式值重用于其他表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！