How Can I Reuse Calculated Fields in MySQL SELECT Queries to Improve Readability and Avoid Repetition?

Reuse the calculation field in the MySQL SELECT query

When dealing with large, complex SQL queries, reuse calculation fields to avoid duplication and improve readability. In mysql, user variables can be used to achieve this purpose.

Consider the following example:

This query attempts to reuse the calculation field Total_sale in the expression f1_percent. However, mysql will return the "unknown list" error because Total_sale is not included in the Sales table.

<code class="language-sql">SELECT 
    s.f1 + s.f2 as total_sale, 
    s.f1 / total_sale as f1_percent
FROM sales s</code>

In order to solve this problem, we can use: = The calculatory symbol gives the calculation value to the user variable:

By add@SELECT @total_sale := s.f1 + s.f2 as total_sale, s.f1 / @total_sale as f1_percent FROM sales s

It should be noted that although this method allows us to reuse the calculation field, it also has some places to pay attention. According to the MySQL document, the value of allocating and reading the user variable in the same statement is an unfarmable behavior, which may produce unexpected results. Therefore, it is recommended to use this technology carefully and use it only when necessary.

The above is the detailed content of How Can I Reuse Calculated Fields in MySQL SELECT Queries to Improve Readability and Avoid Repetition?. For more information, please follow other related articles on the PHP Chinese website!