SQL Server转MySQL：CTE与OrderDetailsJSON查询转换求助

以下是你提供的SQL Server查询对应的MySQL实现：

CREATE TABLE CustomerOrder (
    BusinessEntityID INT, 
    FirstName VARCHAR(50), 
    MiddleName VARCHAR(50), 
    LastName VARCHAR(50), 
    EmailPromotion INT,
    OrderDetailsJSON JSON -- 用JSON类型替代NVARCHAR(MAX)，更适合存储JSON数据
);

WITH CTE_PersonContact AS (
    SELECT
        P.BusinessEntityID, 
        P.FirstName, 
        P.MiddleName, 
        P.LastName, 
        P.EmailPromotion,
        (
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    'SalesOrderID', S.SalesOrderID,
                    'OrderDate', S.OrderDate,
                    'SubTotal', S.SubTotal,
                    'TaxAmt', S.TaxAmt,
                    'TotalDue', S.TotalDue
                )
            ) AS OrderDetailsJSON
            FROM `AdventureWorks2014`.`Sales`.`SalesOrderHeader` S
            WHERE S.CustomerID = P.BusinessEntityID
        ) AS OrderDetailsJSON
    FROM `Person`.`Person` P
)

INSERT INTO CustomerOrder
SELECT
    BusinessEntityID, 
    FirstName, 
    MiddleName, 
    LastName, 
    EmailPromotion, 
    OrderDetailsJSON
FROM CTE_PersonContact
WHERE OrderDetailsJSON IS NOT NULL;

关键转换说明 #

• JSON生成逻辑替换：SQL Server的FOR JSON AUTO在MySQL中需要用JSON_ARRAYAGG() + JSON_OBJECT()组合实现：

  • JSON_OBJECT()将每一行订单数据转换成单个JSON对象，键名手动指定以匹配原SQL Server返回的结构
  • JSON_ARRAYAGG()将多个JSON对象聚合为一个JSON数组，对应FOR JSON AUTO生成的数组结构

• 数据类型调整：

  • MySQL没有NVARCHAR(MAX)类型，推荐用JSON类型存储JSON数据（自带格式校验和JSON操作支持），兼容旧版本可改用LONGTEXT
  • NVARCHAR在MySQL中直接用VARCHAR即可（MySQL 8.0+默认字符集为utf8mb4，完全支持Unicode）

• 对象引用语法：SQL Server的方括号[]替换为MySQL的反引号`，若表名/列名不是关键字，也可省略反引号

• CTE兼容性：MySQL 8.0及以上版本支持WITH CTE语法，写法和SQL Server基本一致，无需额外修改

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