MySQL多UNION查询优化及变量、格式化相关技术咨询

Q1: Can I set @min, @t, and @n variables within a SELECT UNION query? #

Yes, you can set user-defined variables (@min, @t, @n) directly in a SELECT UNION query, but you need to be intentional about when and where you initialize/update them to avoid unexpected behavior.

MySQL evaluates variable assignments row-by-row, so if you’re using variables to calculate running totals or aggregate values across UNION results, initialize them in the first part of the UNION (or a preceding subquery) and reference/update them in subsequent parts. A key tip: use UNION ALL instead of UNION unless you need duplicate removal—this is faster and prevents unintended interference with variable state.

Here’s a simplified example:

SELECT 
    @min := MIN(old_value) AS value,
    'Min Old Value' AS row_label
FROM project_changes
UNION ALL
SELECT 
    @t := SUM(new_value) AS value,
    'Total New Value' AS row_label
FROM project_changes
UNION ALL
SELECT 
    @t / COUNT(*) AS value,
    '平均值' AS row_label
FROM project_changes;

Q2: Can I apply decimal places only to the "平均值" (Average) row instead of all columns? #

Absolutely! Instead of formatting every value in the column, conditionally apply rounding or formatting only to the average row.

If your UNION includes a row labeled '平均值', wrap the average calculation in ROUND() or FORMAT() specifically in that segment of the query:

-- Original rows (no decimal formatting)
SELECT 
    old_value,
    new_value,
    '项目记录' AS row_label
FROM project_changes
UNION ALL
-- Average row with decimal precision
SELECT 
    ROUND(AVG(old_value), 2) AS old_value,
    ROUND(AVG(new_value), 2) AS new_value,
    '平均值' AS row_label
FROM project_changes;

If you’re using variables to compute the average (like @t / @n), apply the rounding directly to that calculation:

SELECT 
    ROUND(@t / @n, 2) AS average_value,
    '平均值' AS row_label

This ensures all original rows retain their raw values, while only the average row gets the specified decimal formatting.

Q3: Incomplete Question #

It looks like your third question was cut off! Could you share more details about what you’re trying to accomplish with the query? For example, are you looking to optimize performance, improve readability, add additional metrics, or handle specific edge cases? The more context you provide, the better I can help you refine the query.

内容的提问来源于stack exchange，提问作者Alex D