如何在子查询中借助另一子查询实现数据排序？SQL技术求助

Got it, let's break down why your notes are sorting alphabetically and fix that.

The issue in your current query is twofold:

1. You're using GROUP BY OUN.note in the subquery, which not only deduplicates your notes but also tells SQL Server to order the results by OUN.note (alphabetical order) by default, since that's the grouped column.
2. The STUFF function is set to remove only the first 1 character, but your subquery prepends ', ' to each note—so you're left with a leading space in your final concatenated string.

Here's the corrected query, assuming you have a column in OutcomeNote that defines the proper order of your notes (like an auto-incrementing noteID, a createdDate, or a dedicated sortOrder column). Replace OUN.noteID with whatever column you need to get the right sequence:

SELECT 
    OC.outcomeKey [Outcome Key], 
    OC.outcome [Result], 
    STUFF((
        SELECT ', ' + OUN.note 
        FROM OutcomeNote AS OUN 
        WHERE OUN.outcomeKey = OC.outcomeKey 
        ORDER BY OUN.noteID -- Use your actual sort column here (e.g., createdDate, sortOrder)
        FOR XML PATH ('')
    ), 1, 2, '') [Outcome Note] 
FROM Outcome AS OC 
GROUP BY OC.outcomeKey, OC.outcome;

Key changes made: #

• Removed the unnecessary JOIN Outcome AS OUT in the subquery (since we're directly filtering OutcomeNote by OC.outcomeKey).
• Dropped the GROUP BY OUN.note from the subquery (unless you specifically need to deduplicate notes—see below for that case).
• Added an explicit ORDER BY in the subquery to control the sequence of notes.
• Adjusted STUFF to remove the first 2 characters (', ') instead of 1, eliminating the leading space.
• Replaced DISTINCT in the outer query with GROUP BY (cleaner for aggregating per outcome).

If you do need to deduplicate notes (only show each unique note once per outcome), you can modify the subquery to get unique notes while preserving your desired order. For example, if you want to keep the first occurrence of each note based on noteID:

SELECT 
    OC.outcomeKey [Outcome Key], 
    OC.outcome [Result], 
    STUFF((
        SELECT ', ' + OUN.note 
        FROM (
            SELECT DISTINCT 
                note, 
                MIN(noteID) AS sortKey -- Get the earliest noteID for each unique note
            FROM OutcomeNote 
            WHERE outcomeKey = OC.outcomeKey
            GROUP BY note
        ) AS OUN 
        ORDER BY OUN.sortKey -- Order by the earliest occurrence
        FOR XML PATH ('')
    ), 1, 2, '') [Outcome Note] 
FROM Outcome AS OC 
GROUP BY OC.outcomeKey, OC.outcome;

This way, you get unique notes ordered by their first appearance, not alphabetically.

Let me know if you don't have a dedicated sort column in OutcomeNote—we can brainstorm other ways to get the right order based on your table structure!

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