MySQL查询转MSSQL遇阻，求:=等语法的等效转换方案

Hey there! I’ve run into this exact headache when migrating queries between MySQL and SQL Server, so let’s break down how to handle that tricky := syntax and get your query working in MSSQL.

First, What Does := Do in MySQL? #

In MySQL, the := operator lets you assign a value to a user-defined variable while also returning that value as part of the query result. For example, you might see something like this for running totals:

SELECT 
    @running_total := @running_total + Amount AS RunningTotal,
    OrderDate
FROM Orders, (SELECT @running_total := 0) AS init
ORDER BY OrderDate;

This initializes a variable, updates it row-by-row, and returns the cumulative total in the result set.

MSSQL Equivalents for := #

MSSQL handles variable assignment differently—here are the most common ways to replicate that behavior:

1. Basic Variable Assignment (Outside Result Sets) #

If you just need to set a variable for later use (not return it in the SELECT), use SET or SELECT:

-- Declare the variable first (required in MSSQL)
DECLARE @running_total INT = 0;

-- Option 1: Use SET for single-value assignment
SET @running_total = (SELECT SUM(Amount) FROM Orders);

-- Option 2: Use SELECT to assign directly from a query
SELECT @running_total = SUM(Amount) FROM Orders;

2. Assign and Return Values in a Result Set #

If you need to both assign a variable and return its value (like the MySQL := pattern), you can structure your query to do both in the SELECT clause—just note that MSSQL doesn’t guarantee the order of column execution, so this works best for simple cases:

DECLARE @current_paper VARCHAR(50);

SELECT 
    @current_paper = Paper, -- Assign to variable
    Paper AS Paper, -- Return the value
    Condition,
    Company,
    Date
FROM YourTable
WHERE Company = 'Company1';

3. Better Alternatives for Row-by-Row Logic #

For common use cases like running totals, row numbering, or concatenating values (like your sample data’s Condition field), MSSQL has built-in window functions that are more reliable than variable assignment (since variable execution order isn’t guaranteed):

Example: Concatenating Condition Values (Matching Your Sample)

If your MySQL query used := to concatenate Condition strings per Company/Date/Paper, use STRING_AGG() (available in SQL Server 2017+):

SELECT 
    Company,
    Date,
    Paper,
    STRING_AGG(Condition, ', ') WITHIN GROUP (ORDER BY Condition) AS CombinedConditions
FROM YourTable
GROUP BY Company, Date, Paper;

Example: Running Totals

Instead of using a variable with :=, use SUM() OVER():

SELECT 
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

Key Notes to Remember #

• MSSQL requires you to declare variables explicitly with DECLARE @var DataType; before using them—MySQL allows implicit declaration.
• Avoid relying on row-by-row variable assignment in MSSQL unless absolutely necessary; window functions are more efficient and predictable.
• If you’re migrating a complex query with multiple variables, consider breaking it into CTEs (Common Table Expressions) or temporary tables to simplify the logic.

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