将MySQL查询改写为SQL Server 2014查询时遇子查询返回多行错误的解决方法

Let's break down what's going wrong with your current query and fix it step by step.

Why You're Seeing the Msg 512 Error #

Your inner subquery is returning multiple rows instead of a single concatenated string for each row in testruns. This happens because:

• The nested SELECT DISTINCT wasn't properly filtered to only the relevant rows for the current testruns.TestJobId
• The outer subquery was incorrectly joining target and versions again without properly aggregating results into one string

Correct SQL Server 2014 Query #

Here's the rewritten query that matches your original MySQL logic and runs without errors:

SELECT 
    (SELECT 
         STUFF(
             (SELECT DISTINCT ', <br>' + CONCAT(v.Name, ': ', v.Version)
              FROM [SwMetrics].[testreportingdebug].[target] t
              LEFT JOIN [SwMetrics].[testreportingdebug].[versions] v 
                  ON v.targetid = t.id
              WHERE t.TestJobId = tr.TestJobId 
                AND v.Name = 'SW'
              FOR XML PATH(''), TYPE
             ).value('.', 'VARCHAR(MAX)'),
             1, 6, '' -- Remove the leading ', <br>' string
         ) AS VersionString
    ) AS NameVersion
FROM [SwMetrics].[testreportingdebug].[testruns] tr;

Key Changes Explained #

• Added table aliases (tr, t, v) to simplify the query and eliminate ambiguity between table references
• Correlated the subquery properly: The t.TestJobId = tr.TestJobId condition ensures we only pull versions for the specific TestJobId from the current row in testruns
• Used TYPE and .value(): This handles special characters (like HTML entities) correctly, which the raw FOR XML PATH('') approach might mangle
• Adjusted STUFF parameters: The leading string we're stripping is ', <br>' (6 characters), so we use 1, 6, '' instead of the incorrect 1,1,'' from your original attempt
• Removed redundant joins: The outer subquery no longer joins tables unnecessarily — all aggregation happens in the nested XML subquery

Bonus: Simpler Option for Later SQL Server Versions #

If you ever upgrade to SQL Server 2017 or newer, you can use the built-in STRING_AGG function which makes this task far cleaner:

-- For SQL Server 2017+ only
SELECT 
    STRING_AGG(DISTINCT CONCAT(v.Name, ': ', v.Version), ', <br>') AS NameVersion
FROM [SwMetrics].[testreportingdebug].[testruns] tr
JOIN [SwMetrics].[testreportingdebug].[target] t ON t.TestJobId = tr.TestJobId
LEFT JOIN [SwMetrics].[testreportingdebug].[versions] v ON v.targetid = t.id AND v.Name = 'SW'
GROUP BY tr.TestJobId, tr.id; -- Include all columns from testruns that you need in the GROUP BY

内容的提问来源于stack exchange，提问作者Mads Sander Høgstrup