X++ SQL 代码转换为 SQL Server 代码：示例及优化

以下是将 X++ SQL 代码转换为 SQL Server 代码的示例：

原始 X++ 代码：

select MtxCostCenter, MtxTaxGroup, MtxTaxItemGroup, sum(MtxInvoiceAmount), sum(MtxTaxAmount)
                    from mtxCustOutsideSalesLine
                    group by dimensionAttributeValueCombination.MainAccountValue
                        , inventPosting.LedgerDimension
                        , mtxCustOutsideSalesLine.MtxCostCenter
                        , mtxCustOutsideSalesLine.MtxTaxGroup
                        , mtxCustOutsideSalesLine.MtxTaxItemGroup
                        , mtxCustOutsideSalesLine.MtxItemId
                    order by dimensionAttributeValueCombination.MainAccountValue
                        , mtxCustOutsideSalesLine.MtxCostCenter
                        , mtxCustOutsideSalesLine.MtxTaxGroup
                        , mtxCustOutsideSalesLine.MtxTaxItemGroup
                    where mtxCustOutsideSalesLine.MtxOutsideSalesId = 'MCOH0000000249'
                    join LedgerDimension from inventPosting
                    where inventPosting.InventAccountType = InventAccountType::SalesRevenue             
                        && inventPosting.ItemCode == InventPostingItemCode::Table
                        && inventPosting.ItemRelation == mtxCustOutsideSalesLine.MtxItemId
                    join MainAccountValue from dimensionAttributeValueCombination
                    where dimensionAttributeValueCombination.RecId == inventPosting.LedgerDimension

转换后的 SQL Server 代码：

SELECT mtxCustOutsideSalesLine.MtxCostCenter, mtxCustOutsideSalesLine.MtxTaxGroup, mtxCustOutsideSalesLine.MtxTaxItemGroup, 
       SUM(mtxCustOutsideSalesLine.MtxInvoiceAmount), SUM(mtxCustOutsideSalesLine.MtxTaxAmount)
FROM mtxCustOutsideSalesLine
INNER JOIN inventPosting ON inventPosting.ItemRelation = mtxCustOutsideSalesLine.MtxItemId
INNER JOIN dimensionAttributeValueCombination ON dimensionAttributeValueCombination.RecId = inventPosting.LedgerDimension
WHERE mtxCustOutsideSalesLine.MtxOutsideSalesId = 'MCOH0000000249'
AND inventPosting.InventAccountType = 'SalesRevenue'
AND inventPosting.ItemCode = 'Table'
GROUP BY dimensionAttributeValueCombination.MainAccountValue, inventPosting.LedgerDimension, 
         mtxCustOutsideSalesLine.MtxCostCenter, mtxCustOutsideSalesLine.MtxTaxGroup, mtxCustOutsideSalesLine.MtxTaxItemGroup, 
         mtxCustOutsideSalesLine.MtxItemId
ORDER BY dimensionAttributeValueCombination.MainAccountValue, mtxCustOutsideSalesLine.MtxCostCenter, 
         mtxCustOutsideSalesLine.MtxTaxGroup, mtxCustOutsideSalesLine.MtxTaxItemGroup

解释：

• X++ 中的 join 语句在 SQL Server 中使用 INNER JOIN 关键字替换。
• X++ 中的 && 运算符在 SQL Server 中使用 AND 关键字替换。
• X++ 中的枚举类型（如 InventAccountType::SalesRevenue）在 SQL Server 中使用字符串常量替换。

优化：

• 可以根据实际情况对查询进行进一步优化，例如添加索引、使用视图等。

注意事项：

• 转换后的 SQL Server 代码可能需要根据实际情况进行调整。
• 确保数据类型和字段名称与 SQL Server 数据库中的定义一致。
• 在转换之前，应备份原始 X++ 代码，以防意外情况。