你可以使用 ORDER BY 以及 WHERE 子查询中的子句。像这样：

SELECT ID,
       STUFF( (SELECT ';' + t1.CTY
               FROM country_tbl t1
               WHERE t2.ID = t1.ID AND
                     NOT (t1.id = '123456' AND t1.CTY = 'England')
               ORDER BY t1.CTY
               FOR XML PATH('')
              ), 1, 1, ''
            ) AS aggregation
FROM (SELECT DISTINCT id
      FROM master_tbl t2
      WHERE t2.ID IN ('123456','123457')
     ) t2
ORDER BY T2.ID;

我也换了衣服 GROUP BY 在外部查询中 SELECT DISTINCT 以提高性能。

如果我没听错的话，你想把t2中的城市列出来，除了t1中那些ID相同的城市，按升序排列。如果是这样的话，那你就很接近了，但还不是很接近。
首先，创建并填充示例表（请在以后的问题中保存此步骤）：

DECLARE @t1 AS TABLE (
    [ID] int, 
    [Country] varchar(7)
);

INSERT INTO @t1 ([ID], [Country]) VALUES
(123456, 'England'),
(123457, 'Canada');

DECLARE @t2 AS TABLE (
    [ID] int, 
    [CTY] varchar(7)
);

INSERT INTO @t2 ([ID], [CTY]) VALUES
(123456, 'England'),
(123456, 'Japan'),
(123456, 'France'),
(123456, 'Canada'),
(123457, 'England'),
(123457, 'Japan'),
(123457, 'France');

查询：

SELECT DISTINCT Id,
        STUFF((
            SELECT ';' + t2.CTY
            FROM @t2 As t2
            LEFT JOIN @t1 As t1
                ON t2.Id = t1.Id
                AND t2.CTY = t1.Country
            WHERE t2.ID = t0.ID 
            AND t1.Id IS NULL
            ORDER BY t2.CTY
            FOR XML PATH('')),1,1,'') AS aggregation
FROM @t2 As t0

结果：

Id      aggregation
123456  Canada;France;Japan
123457  England;France;Japan